excelvbacopyrangecell

Copy/paste values without changing active workbook/sheet


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:

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.


Solution

  • 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