VBA Error Subscript out of range in for loop used to copy/paste

Question

I keep getting an error message for my for loop. What it is supposed to do is compare a variable to the first column for every row for a given worksheet and copy it if it matches, then paste it to a worksheet in a different workbook but for some reason I keep getting "Run-time error '9': Subscript out of Range" for the line referencing the new workbook/worksheet.

My for loop so far:

For i = 1 To Sheets(1).UsedRange.Rows.Count

    If Sheets(1).Cells(i, 1).Value Like dateRange & "*" Then
       Sheets(1).Rows(i).Copy
       Workbooks(destinationWorkbook).Worksheets("Sheet2").Rows(i).Paste 'Debug shows the error is here
    End If

Next i

In the previous code I define the variable as:

destinationWorkbook = "C:\Users\lbran\Desktop\Book1.xlsm"

I have also tried using Workbooks(destinationWorkbook).Worksheets("Sheet2").Cells(i, 1).Paste

I'm very new to VBA so I'm not sure if I'm referencing the pasting to worksheet correctly but I know it does exist as a new, empty sheet in the workbook

Harassed Dad Harassed Dad · Accepted Answer · 2017-12-06T16:55:39

Once you've opened a workbook it's name is just the filename it was saved under, not the path. So you would do

  Workbooks.open(destinationWorkbook)

But then refer to it as

  Workbooks("Book1.xlsm")

But I wonder if you are trying to copy to a new workbook that isn't on your hard drive? In which case you would write

     Workbooks.add
     WorkBooks("book1")

There wouldn't be a file extension because you haven't saved it yet. You can avoid all these problems by using an object variable

 Dim wb as workbook
Set wb = workbooks.open(destinationworkbook)
or
Set wb = workbooks.add

and then just wb to refer to the workbook

VBA Error Subscript out of range in for loop used to copy/paste

1 Answers