Copy/Paste Macro Throwing an Error

[rob51852]

Hi,

I've put together a macro to copy/paste entire rows if the cell in column A contains a value. The aim is to have the macro paste in the first empty row in a sheet called Store. The problem is I keep getting an error - the debugger highlights the Rows.Count line.

Can anyone see what the issue is?

Thanks

Sub CopyToStore()
    Dim c As Range
    Dim Source As Worksheet
    Dim Target As Worksheet

    Set Source = ThisWorkbook.Worksheets("Input")
    Set Target = ThisWorkbook.Worksheets("Store")

    For Each c In Source.Range("A8:A300")
        If c <> "" Then
           Source.Rows(c.Row).Copy
           Target.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    Next c
End Sub

 

[Anthony47]

It should be Rows.Count, not .Rows.Count

Bye

 

[rob51852]

Thanks Anthony, that's fixed it.

 

[Anthony47]

Good...
Using the "dot" assumes that an Object had set as the parent of the Rows property, and this is not the case in your code. The plain Rows will refer to the default parent, ie the active sheet

Bye

 

[steve the fish]

It really should be Target.Rows.Count as Rows.Count could error on occasion if unlikely.

 

[Anthony47]

Steve is correct.
For example you would have an error if your active worksheet has an xlsx/xlsm/xlsb (1 mill Rows) format but the workbook that contains the macro (ie the one you are working with) has an xls (65kRows) format.

Bye