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.
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