I have a macro which copies specific information from a target row on one
worksheet, and copies that information to the first blank cell it finds on a
designated worksheet. However, I need for it to begin looking for the first
blank cell on row 12. Here is the macro I currently have:

Sub CopyRetailerInfo()
Dim BlankCell As Integer

Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
Worksheets("Field Activity Report").Select
BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity
Report").Range("D:D")) + 1
Worksheets("Field Activity Report").Cells(BlankCell, 4).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Worksheets("Survey Spreadsheet").Select
End Sub

Can you help correct this macro so it begins on row 12? Thanks for your help.

One way:

Public Sub CopyRetailerInfo()
Dim rDest As Range
With Worksheets("Field Activity Report")
Set rDest = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
If rDest.Row < 12 Then Set rDest = .Range("D12")
End With
rDest.Resize(1, 5).Value = ActiveCell.Resize(1, 5).Value
End Sub

Note that selection/activation is almost never needed. Using the range
objects directly is faster, generates smaller code, and IMO, is easier
to maintain.



In article ,
"Larry" wrote:

I have a macro which copies specific information from a target row on one
worksheet, and copies that information to the first blank cell it finds on a
designated worksheet. However, I need for it to begin looking for the first
blank cell on row 12. Here is the macro I currently have:

Sub CopyRetailerInfo()
Dim BlankCell As Integer

Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
Worksheets("Field Activity Report").Select
BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity
Report").Range("D:D")) + 1
Worksheets("Field Activity Report").Cells(BlankCell, 4).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Worksheets("Survey Spreadsheet").Select
End Sub

Can you help correct this macro so it begins on row 12? Thanks for your help.

Thanks, that does work and your information was exactly right. I found that
changing Range("D:D")) + 1 to Range("D:D")) +10 moved the information down
to the row I was shooting for. Thanks for all your help!

"JE McGimpsey" wrote:

One way:

Public Sub CopyRetailerInfo()
Dim rDest As Range
With Worksheets("Field Activity Report")
Set rDest = .Range("D" & Rows.Count).End(xlUp).Offset(1, 0)
If rDest.Row < 12 Then Set rDest = .Range("D12")
End With
rDest.Resize(1, 5).Value = ActiveCell.Resize(1, 5).Value
End Sub

Note that selection/activation is almost never needed. Using the range
objects directly is faster, generates smaller code, and IMO, is easier
to maintain.



In article ,
"Larry" wrote:

I have a macro which copies specific information from a target row on one
worksheet, and copies that information to the first blank cell it finds on a
designated worksheet. However, I need for it to begin looking for the first
blank cell on row 12. Here is the macro I currently have:

Sub CopyRetailerInfo()
Dim BlankCell As Integer

Range(ActiveCell, ActiveCell.Offset(0, 4)).Copy
Worksheets("Field Activity Report").Select
BlankCell = Application.WorksheetFunction.CountA(Worksheets("F ield Activity
Report").Range("D:D")) + 1
Worksheets("Field Activity Report").Cells(BlankCell, 4).Select
Selection.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Worksheets("Survey Spreadsheet").Select
End Sub

Can you help correct this macro so it begins on row 12? Thanks for your help.