I used the link below and went through the steps. I
modified the script as shown. Where my excel files are in
a file called test on the c drive. When I hit run nothing
happens what should I change to make it work?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=221
Option Explicit
Sub CombineFiles()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\" 'Change as needed
FileName = Dir(Path & "\*.xls", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" &
FileName)
For Each WS In Wkb.Worksheets
WS.Copy After:=ThisWorkbook.Sheets
(ThisWorkbook.Sheets.Count)
Next WS
Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
