始终定义和设置对所有工作簿和表格的引用

使用多个打开的工作簿时，每个工作簿可能有多个工作表，最安全的是定义和设置对所有工作簿和工作表的引用。

不要依赖 ActiveWorkbook 或 ActiveSheet，因为它们可能会被用户更改。

下面的代码示例演示了如何从复制“的范围 Raw_Data ”片材在“ Data.xlsx ”工作簿“ Refined_Data 在”“片 Results.xlsx ”工作簿。

该过程还演示了如何在不使用 Select 方法的情况下进行复制和粘贴。

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