Copy/Paste Macro Throwing an Error

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
175
Office Version
  1. 2016
Platform
  1. Windows
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

VBA Code:
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

rob51852

Board Regular
Joined
Jun 27, 2016
Messages
175
Office Version
  1. 2016
Platform
  1. Windows
Thanks Anthony, that's fixed it.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
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

Well-known Member
Joined
Oct 20, 2009
Messages
8,388
Office Version
  1. 365
Platform
  1. Windows
It really should be Target.Rows.Count as Rows.Count could error on occasion if unlikely.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,147
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,110
Members
417,073
Latest member
CBG_Christian

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top