copy sheets from multiple workbooks to one workbook - error 1004 1

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

Hi,

"I have 19 spreadsheets"

You have 19 workbooks

You're shooting yourself in the foot, I believe, by setting SECOND Excel Application object. Use the original one you already set...

Sub CopySheet(wsName As String)

    Dim xlsBook1 As Object
    Dim xlsSheet1 As Object
    
    
    'Source Workbook
    Set xlsBook1 = xlsApp[b][red]2[/red][/b].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

Skip,

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

 

[BitZero]

That worked! The macro runs, and my foot is feeling much better. Thanks

 

[SkipVought]

Jes' the way us Texans talk.

Skip,

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