Copy/paste values without changing active workbook/sheet

Question

I can copy cells by activating the worksheet first.

Shouldn't I be able to copy without activating the worksheet?

Sub test()
    Dim wbSource, wbTarget As Workbook
    Dim wsSource As Worksheet
    Set wbSource = ThisWorkbook
    Set wsSource = wbSource.Worksheets(1)
    
    Set wbTarget = Workbooks.Add
    Set wsTarget = wbTarget.Worksheets(1)
    
    'Doesnt work
    wsSource.Range(Cells(1, 1), Cells(2, 2)).Copy
    
    'Works
    'wsSource.Activate
    'Range(Cells(1, 1), Cells(2, 2)).Copy
    
    wsTarget.Cells(1, 1).PasteSpecial
End Sub

I get:

"Run-time error '1004':"
"Method 'Range' of object'_Worksheet' failed"

Swapping out the part which doesn't work with the code which does (commented out) results in:

• Excel flickering through every sheet (if the data was spread out on several sheets in wbSource)
• If a user performs an input which changes the active sheet, the macro can break (copy/paste the wrong values).

Swapping out

wsSource.Range(Cells(1, 1), Cells(2, 2)).Copy

with

wsSource.Range("A1:B2").Copy

works, but I need to change the rows and columns dynamically.

I am trying to not alternate between active workbooks, so I am not dependent on which workbook/worksheet is currently active (selected window).

Ultimately I'm trying to avoid; a user clicks on anything that changes the active workbook/worksheet while a macro is running and messes up the sequence.

Answer 1

When Range is defined through the Cells object they also must be qualified like in the edited code

Sub test()
    Dim wbSource, wbTarget As Workbook
    Dim wsSource As Worksheet
    Set wbSource = ThisWorkbook
    Set wsSource = wbSource.Worksheets(1)
    
    Set wbTarget = Workbooks.Add
    Set wsTarget = wbTarget.Worksheets(1)
    
    'Doesnt work     'edited
    wsSource.Range(wsSource.Cells(1, 1), wsSource.Cells(2, 2)).Copy
    
    'Works
    'wsSource.Activate
    'Range(Cells(1, 1), Cells(2, 2)).Copy
    
    
    wsTarget.Cells(1, 1).PasteSpecial
End Sub