Damien,

"copy...without opening the source"
Not that I know of.
Why? Are you trying to make the code run faster?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Damien McBain"
wrote in message
Hi,
I have a for...next loop which runs through a series of workbook names in a
worksheet, opens a workbook, copies a worksheet into the main workbook then
closes the workbook again.
Is it possible to copy the worksheet from the source workbook into the
destination workbook without opening the source?
Here's the existing code:
=======================
Sub ImportSummaries()
On Error GoTo Hell

Application.ScreenUpdating = False

Dim wbName
Dim PathName
Dim YearPeriod
Dim WSName
Dim wk

YearPeriod = Worksheets("Summary").Range("YearPeriod")
PathName = "C:\OpsReport\" & YearPeriod & "\"
wk = Worksheets("Summary").Range("Weekno")

For Each branch In Worksheets("Tables").Range("BusAreaList")

wbName = branch.Text & " " & YearPeriod & " wk " & wk & ".xls"
WSName = branch.Text

If wsExists(CStr(branch)) Then
Application.DisplayAlerts = False
Worksheets(CStr(branch.Text)).Delete
Application.DisplayAlerts = True
End If
If Not CBool(Len(Dir(CStr(PathName & wbName)))) Then
MsgBox "There's no data to import for " & branch.Text, , "Operating Report"
Else
Workbooks.Open CStr(PathName & wbName)
Workbooks(CStr(wbName)).Worksheets(CStr(branch)).C opy _
After:=Workbooks("Operating Report - Southern Summary.xls"). _
Worksheets(Worksheets.Count)
Workbooks(CStr(wbName)).Close

End If
Next branch
Gout:
Application.ScreenUpdating = True
Exit Sub
Hell:
MsgBox Err.Description, , "Operating Report"
Resume Gout
End Sub
============================
TIA
Damien