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

use .PasteSpecial

Scott Craner
– Scott Craner

2017-12-06 16:53:34 +00:00
Commented Dec 6, 2017 at 16:53 — Scott Craner
– Scott Craner, Commented Dec 6, 2017 at 16:53
Using .PasteSpecial doesn't change the error

LillyBran
– LillyBran

2017-12-06 16:59:16 +00:00
Commented Dec 6, 2017 at 16:59 — LillyBran
– LillyBran, Commented Dec 6, 2017 at 16:59

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

1

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

answered Dec 6, 2017 at 16:55

Harassed Dad

4,7141 gold badge13 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related