have 12 .xls files, need to combine them

[lennydude]

Hi there..........

I have xls files that i do on a monthly basis. One file for each month.

I have been trying to find out how to combine them into a single file.

They are monthly sales, and i would like to keep 1 for each month and 1 for the year which has all 12 months with each month as a separate "workbook".

Thanks for any help.............

Lenny

 

[Rekd]

This should work. I got the core code from here and abandoned it before getting it dialed in, but it should do what you want with minor modifications (to filename and path).

Sub CopyAllBooks() 
'Copy all sheets from all books in a folder to one book.
'To Do:
'       Clean up code
'       Add Browse for Folder
'       Commonize sheet names
'           Use first 8 from file name and
'           first 8 (or?) from sheet name and
'           date info or?
'       Create index of sheets on Sheet1
'       Use current .xls to grab sheets instead of fixed file
Dim z  As Long, e As Long, b As Long
Dim f As String, path As String
Dim m As String, wb As String
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Worksheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
path = Cells(1, 2)
m = path & "Your File Name.xls"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(1, 0).Select
f = Dir()
Loop
ChDir (path)
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Worksheets("Sheet1").Cells(e, 1) <> ActiveWorkbook.Name Then
wb = Worksheets("Sheet1").Cells(e, 1)
Workbooks.Open Filename:=Worksheets("Sheet1").Cells(e, 1)
For b = 1 To Worksheets.Count
Worksheets(b).Activate
Worksheets(b).Copy After:=Workbooks("Your File Name.xls").Sheets(1)
Workbooks(wb).Activate
Next b
ActiveWorkbook.Close False
End If
Next e
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Collating is complete.", vbExclamation
End Sub

Create a new workbook in the directory with the files, save it, then run this.

 

[smakcc]

Thanks worked for me.