want to copy a sheet from one workbook to another

[BitZero]

I have 19 spreadsheets, and I'm trying to copy the first sheet of each of these into one workbook that has 19 sheets. When I run the macro, I'm getting this message the first time it tries to execute the ".copy":

Run-time error '1004':
Copy method of Worksheet class failed.

Can anyone tell me what I'm doing wrong? Is there a better way to do this?

Thanks


Here's the code I have in Module1 (excel 2003):

Option Explicit

Dim xlsApp2 As Object
Dim xlsBook2 As Object
Dim xlsSheet2 As Object


Sub CopyAll()

'Destination Workbook
Set xlsApp2 = CreateObject("excel.application")
Set xlsBook2 = xlsApp2.Workbooks.Open("c:\Sales\temp\ShipmentData.xls")
xlsApp2.DisplayAlerts = False
xlsApp2.Visible = False


CopySheet ("s01")
CopySheet ("s45")
CopySheet ("s47")
CopySheet ("s49")
CopySheet ("s50")
CopySheet ("s51")
CopySheet ("s56")
CopySheet ("s57")
CopySheet ("s58")
CopySheet ("s59")
CopySheet ("s60")
CopySheet ("s65")
CopySheet ("s66")
CopySheet ("s68")
CopySheet ("s70")
CopySheet ("s71")
CopySheet ("s72")
CopySheet ("s75")
CopySheet ("s76")

' CLOSE:
xlsBook2.Close SaveChanges:=True
Set xlsBook2 = Nothing
xlsApp2.Quit
Set xlsApp2 = Nothing

End Sub

Sub CopySheet(wsName As String)

Dim xlsApp1 As Object
Dim xlsBook1 As Object
Dim xlsSheet1 As Object


'Source Workbook
Set xlsApp1 = CreateObject("excel.application")
Set xlsBook1 = xlsApp1.Workbooks.Open("C:\Sales\temp\" & wsName & ".xls")
' xlsApp1.Visible = False
' xlsApp1.DisplayAlerts = False

'COPY
xlsBook1.Worksheets(1).Copy after:=xlsBook2.Worksheets(Worksheets.Count)

' CLOSE
xlsBook1.Close
Set xlsBook1 = Nothing
xlsApp1.Quit
Set xlsApp1 = Nothing

End Sub

 

[SkipVought]

Please post VBA code questions in Forum707.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[BitZero]

Sorry. It's been resubmitted.