Macros to combine worksheets from different workbooks

[Yuna]

Hi there,
I have about 30 workbooks and they are all in a same format (same 9 sheets in the workbook):
sheet 1-"Calc sheet"
sheet 2-"summary"
sheet 3-"table 1"
sheet 4-"table 3"
sheet 5-"table 6"
sheet 6-"table 7"
sheet 7-"table 8"
sheet 8-"table 9"
sheet 9-'table 10&11"

Sheet 1 is the calculation sheet with a lot of fomulars and it reads the data from sheet 3 to sheet 9. Sheet 2 is the "summary" table and reads calculation results on sheet 1.

I want to combine all the "summary" sheet from the 30 workbooks into one single workbook (a workbook with 30 "summary" sheet). And I also trying to rename the sheet from "summary" to something related to the workbook's name.

I have a code here, it worked fine in the excel 2003 version (sometimes it failed to rename the sheet name which is still OK). I am having excel 2007 version now and it doesn't work at all any more.

Is there someone can understand the code and if it is possible to improve the code?

Thanks a lot in advance.

Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

SaveFileLocation = InputBox("Where are the files to compile? Example: D:\File\") '"D:\511F\Modelling\System Performance\WWF Performance\Additional Work\Macros to extract OF statistics\" '
If SaveFileLocation = "" Then End

On Error Resume Next

' For i = 1 To ActiveWorkbook.Worksheets.Count - 1
' Sheets(i).Delete
' Next i

Set wbCodeBook = ActiveWorkbook 'ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = SaveFileLocation
.FileType = msoFileTypeExcelWorkbooks
'.Filename = " Book*.xls"

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

BookName = ActiveWorkbook.Name
' YourSheetName = Left(BookName, Application.WorksheetFunction.Find(" ", BookName) - 1)
wbResults.Sheets("Summary").Copy After:=wbCodeBook.Sheets(wbCodeBook.Worksheets.Count)
'wbCodeBook.Sheets(YourSheetName).Name = wbCodeBook.Sheets(1).Range("A1")
wbResults.Close SaveChanges:=False

Next lCount
' wbCodeBook.Close SaveChanges:=True
End If
End With

For i = 1 To ActiveWorkbook.Worksheets.Count
Sheets(i).Activate
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Next i

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub