Automation error

[sandy armstrong]

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
'By Tommy Miles
'This sample goes through all the Excel files in a specified directory and combines theminto
'a single workbook. It renames the sheets based on the name of the original workbook:
Sub CombineWorkbook s()
Dim CurFile As String, DirLoc As String
Dim DestWb As Workbook
Dim ws As Object 'allows for different sheet types

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

Application.Scr eenUpdating = False
Application.Ena bleEvents = False

Set DestWb = Workbooks.Add(x lWorksheet)

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

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

For Each ws In OrigWb.Sheets
ws.Copy After:=DestWb.S heets(DestWb.Sh eets.Count)

If OrigWb.Sheets.C ount > 1 Then
DestWb.Sheets(D estWb.Sheets.Co unt).Name = CurFile & ws.Index
Else
DestWb.Sheets(D estWb.Sheets.Co unt).Name = CurFile
End If
Next

OrigWb.Close SaveChanges:=Fa lse
CurFile = Dir
Loop

Application.Dis playAlerts = False
DestWb.Sheets(1 ).Delete
Application.Dis playAlerts = True

Application.Scr eenUpdating = True
Application.Ena bleEvents = True

Set DestWb = Nothing

End Sub

[Rabbit]

It would help to know what line the error is on.

[Stewart Ross]

Unless you have object variable ThisWorkbook defined globally it is certainly not defined or set in line #4:

DirLoc = ThisWorkbook.path & "\tst\" 'location of files

As Rabbit said, it helps to know what line the error is on.

-Stewart