Specific Row Designation

[Guest]

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("Field Activity
Report").Range("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.

 

[JE McGimpsey]

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.

 

[Guest]

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