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
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
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