Automation error

Sanbiz94

New Member
Joined
Sep 26, 2011
Messages
29
:) 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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
When the error occurs click Debug. Which line of code is highlighted?
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,220
Members
448,554
Latest member
Gleisner2

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
Back
Top