Loop through columns and copy a changing range of cells without activating the sheet

Question

I am trying to copy a range of cells that change in a loop. The range then is pasted in another workbook. I would like to not have to Activate the sheet and the workbook each time. My code now looks like this

For i = 1 to 5
Dim Frontiere As Workbook
Set Frontiere = Workbooks("Frontiere.xlsx")
Dim Summary as Workbook
Set Summary = Workbooks("Summary.xlsx")
                
            Dim Variables As Worksheet
            Set Variables = Frontiere.Worksheets("Variables")
            Dim Scenarios as Worksheet
            Set Scenarios = Summary.Worksheets("Scenarios")

        ' Copy new variable
                Scenarios.Range(Cells(7, i), Cells(57, i + 1)).Copy
                Variables.Cells(6, 12).PasteSpecial Paste:=xlPasteValues

Next i

When I try this, I get the Error message "Method 'Range' of object'_Worksheet' failed, coming fron the line "Scenarios.Range(Cells(7, i), Cells(57, i + 1)).Copy"

I would really like not having to Activate the worksheet each time since I have around 20 other ranges to copy and paste for each loop.

Thanks

You are pasting into the same range in your loop. So, even though you are copying 5 times, the first 4 will be overwritten by the last one. — basodre, Dec 23 '20 at 18:36
Yeah I tried to simply the real code. In the real code I copy in the same cells but in different workbooks each loop. — TristB, Dec 23 '20 at 21:26

urdearboy · Answer 1 · 2020-12-23T17:35:18.790

You can declare the workbook and worksheet in a single line to help clean this up a little. If you ever need to refer to a workbook property this shortcut may not be worth it though. You should also avoid doing anything in the loop that only needs to be done once (i.e. no need to continuously Dim your worksheet variables and then Set them to the same sheet inside every loop)

I adjusted the sheet name variables to be a littler shorter so the references don't take up as much space. You could also just use a With block.

Your main problem is unqualified objects - this is especially true when working across many workbooks & sheets. To avoid the active sheet/book having any impact on the code you should make sure all objects are qualified. I.E. all worksheets are qualified with a workbook. All range & cells objects are qualified with a worksheet. Your issue is likely the two instances of Cells that are unqualified in your .Copy line.

Sub Try()

Dim wsV As Worksheet: Set wsV = Workbooks("Frontiere.xlsx").Sheets("Variables")
Dim wsS As Worksheet: Set wsS = Workbooks("Summary.xlsx").Sheets("Scenarios")

Dim i As Long

For i = 1 To 5
    wsS.Range(wsS.Cells(7, i), wsS.Cells(57, i + 2)).Copy
    wsV.Cells(6, 12).PasteSpecial xlPasteValues
Next i

End Sub

More details on unqualified Cells errors can be found in this solution. In short your Cells are pointing to the sheet where the code exists OR the active sheet if your code is in a module or 'ThisWorkbook'. It sounds like neither of those is the right sheet, hence, to copy without the sheet being active you must fully qualify the object.

score 1 · Answer 2 · answered Dec 23 '20 at 18:28

1

You can simply do this to avoid using the clipboard:

wsV.Cells(6, 12).Value = Scenarios.Range(Cells(7, i), Cells(57, i + 1)).Value

answered Dec 23 '20 at 18:28

Tarik

10,810
2
26
40

Loop through columns and copy a changing range of cells without activating the sheet

2 Answers2