excel-vba Always define and set references to all Workbooks and Sheets


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 _

End Sub