始終定義和設定對所有工作簿和表格的引用
使用多個開啟的工作簿時,每個工作簿可能有多個工作表,最安全的是定義和設定對所有工作簿和工作表的引用。
不要依賴 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