Automation error

Sanbiz94

New Member
Joined
Sep 26, 2011
Messages
28
:) Hello everyone I have this code but i keep getting Automation error. what Im tritng to accomplish is a marco that will copy worksheets into a summary and not remove the source in the orginal file


All my workbook have data all on sheet one all have the same headings all. and all in the same folder Someone please help...
Option Explicit

'Combine Workbooks
Sub CombineWorkbooks()
Dim CurFile As String, DirLoc As String
Dim DestWb As Workbook
Dim ws As Object 'allows for different sheet types

DirLoc = ThisWorkbook.path & "\tst\" 'location of files
CurFile = Dir(DirLoc & "*.xls")

Application.ScreenUpdating = False
Application.EnableEvents = False

Set DestWb = Workbooks.Add(xlWorksheet)

Do While CurFile <> vbNullString
Dim OrigWb As Workbook
Set OrigWb = Workbooks.Open(filename:=DirLoc & CurFile, ReadOnly:=True)

' Limit to valid sheet names and remove .xls*
CurFile = Left(Left(CurFile, Len(CurFile) - 5), 29)

For Each ws In OrigWb.Sheets
ws.Copy After:=DestWb.Sheets(DestWb.Sheets.Count)

If OrigWb.Sheets.Count > 1 Then
DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile & ws.Index
Else
DestWb.Sheets(DestWb.Sheets.Count).Name = CurFile
End If
Next

OrigWb.Close SaveChanges:=False
CurFile = Dir
Loop

Application.DisplayAlerts = False
DestWb.Sheets(1).Delete
Application.DisplayAlerts = True

Application.ScreenUpdating = True
Application.EnableEvents = True

Set DestWb = Nothing

End Sub
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
When the error occurs click Debug. Which line of code is highlighted?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,655
Messages
5,597,384
Members
414,141
Latest member
Joey_T92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top