Copy/Paste in VBA Loop

Question

I have multiple sheets in Excel that contain columns of data. What I'm trying to do is copy and paste all Columns D from the data worksheets into another new worksheet, all Columns E from the data worksheets into another worksheet, etc. I'm able to create 7 new Excel Worksheets to collect the data and the data worksheets start with Worksheet 8 and on. Here is a snip of my code:

For t = 8 To tabs

    Sheets(t).Range("D1:D2544").Copy Worksheets("OCM_VMonM24").Range(Cells(1, t - 6), Cells(2544, t - 6))
    Sheets(t).Range("E1:E2544").Copy Worksheets("OCM_VMonM12").Range(Cells(1, t - 6), Cells(2544, t - 6))
    Sheets(t).Range("F1:F2544").Copy Worksheets("OCM_VMonP24").Range(Cells(1, t - 6), Cells(2544, t - 6))
Next t

It works fine for the first line in the loop however when it attempts to copy Column E (second line of code in the For Loop) it fails. I'm new to VBA and can't figure out what I'm doing wrong.

Run-time error '1004': Application-defined or object-defined error — The Don, Dec 01 '15 at 17:39

Scott Craner · Accepted Answer · 2015-12-01T17:53:06.363

Your paste area needs more qualification Change:

Worksheets("OCM_VMonM24").Range(Cells(1, t - 6), Cells(2544, t - 6))

to this

Worksheets("OCM_VMonM24").Range(Worksheets("OCM_VMonM24").Cells(1, t - 6), Worksheets("OCM_VMonM24").Cells(2544, t - 6))

Change each of the three paste ranges to reflect the changes I did to the first one.

If you do not qualify the Cells reference it looks at the active sheet. So you are calling a range from one sheet but referencing cells from another.

Copy/Paste in VBA Loop

1 Answers1