Two part question
1.
Why doesn’t this work?
I can copy cells by activating the worksheet first, but shouldn’t I be able to copy it 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
The error I get is:
“Run-time error ‘1004’:”
“Method ‘Range’ of object’_Worksheet’ failed”
Swapping out the part which doesnt work with the code which does (commented out) results in excel flickers through every sheet (if the data was spread out on several sheets in wbSource) and if a user performs an input which changes the active sheet, the macro can break (copy/paste the wrong values).
2.
What I am trying to do is not needing to alternating between active workbooks, so I am not dependent on which workbook/worksheet is currently active (selected window).
Ultimately what I’m trying to avoid is if a user clicks on anything and by that changing the active workbook/worksheet while a macro is running and messes up the sequence.
Vincent is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.