Copy Data without Activating a Tab

[zenjah]

Hi there

I have a code that copies filtered data from various source files and pastes into a master workbook

for the copying part the codes are provided below



wb.Sheets("zzz").Columns("A:AA").EntireColumn.Hidden = False
wb.Sheets("zzz").Range("A3", Range("AA3").End(xlDown)).Copy


however, this only works when the source spreadsheet has the "zzz" tab being the active tab
if for example "yyy" was the active tab when the source spreadsheet was saved it will come up with an error message instead

I think this can be resolved by adding



wb.Sheets("***").Activate


but is there any better way to do this?
thanks for your help

[Kenneth Hobs]

With wb.Sheets("zzz")
.Range("A3", .Range("AA3").End(xlDown)).Copy
End With

[Paul_Hossler]

If I have WB_1 and WB_2 open and the macro is in WB_1, then I seem to be able to copy from an inactive tab in WB_1 to different sheets in WB_2




Option Explicit

'macro in WB_1.xlsm
Sub CopyFrom1to2()
Dim wb1 As Workbook, wb2 As Workbook
Dim r As Range

Set wb1 = ThisWorkbook
Set wb2 = Workbooks("WB_2.xlsx")

'make other ws in wb1 active
Worksheets("aaa").Select
MsgBox ActiveSheet.Name

Set r = Worksheets("zzz").Range("A1")
Set r = Range(r, r.End(xlDown))
MsgBox r.Address(1, 1, 1, 1)

r.Copy wb2.Worksheets("aaa").Range("A1")
r.Copy wb2.Worksheets("aaa").Range("C1")

r.Copy wb2.Worksheets("zzz").Range("A1")
End Sub

[zenjah]

thanks guys, I have incorporated your suggestions, thanks heaps for your help