excel-vba VBA Best Practices Always define and set references to all Workbooks and Sheets
Example
When working with multiple open Workbooks, each of which may have multiple Sheets, it’s safest to define and set reference to all Workbooks and Sheets.
Don't rely on ActiveWorkbook or ActiveSheet as they might be changed by the user.
The following code example demonstrates how to copy a range from “Raw_Data” sheet in the “Data.xlsx” workbook to “Refined_Data” sheet in the “Results.xlsx” workbook.
The procedure also demonstrates how to copy and paste without using the Select method.
Option Explicit
Sub CopyRanges_BetweenShts()
Dim wbSrc As Workbook
Dim wbDest As Workbook
Dim shtCopy As Worksheet
Dim shtPaste As Worksheet
' set reference to all workbooks by name, don't rely on ActiveWorkbook
Set wbSrc = Workbooks("Data.xlsx")
Set wbDest = Workbooks("Results.xlsx")
' set reference to all sheets by name, don't rely on ActiveSheet
Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
' copy range from "Data" workbook to "Results" workbook without using Select
shtCopy.Range("A1:C10").Copy _
Destination:=shtPaste.Range("A1")
End Sub
