Merge Multiple Single Worksheet Excel Files into one file in separ

[dbguy11]

I have several single sheeted workbooks that I need to merge into one file
and still maintaining each worksheet into its own tab. I use to have an
add-in or macro but lost it when I got a new pc.

Thanks in advance.

 

[Roger Govier]

Hi

This may get you started.
I Create a new sheet first, called "All Data" and copy the header row from
one of the sheets that is being combined.
Then run the macro

Sub CombineSheets()

Dim Sht As Worksheet, SummarySht As Worksheet
Dim NewRow As Long, LastRow As Long
Const Lastcol = "Z" 'Set for last column of data
Const SourceCol = "AA" ' next column to above
Application.ScreenUpdating = False
NewRow = 2
Set SummarySht = Sheets("All Data")
SummarySht.Range("2:65536").Delete

For Each Sht In ThisWorkbook.Sheets
'Check it is not a Report or Data Sheet
If InStr(Sht.Name, "Report") = 0 _
And InStr(Sht.Name, "Data") = 0 Then

LastRow = Sht.Range("A" & Rows.Count).End(xlUp).Row
If NewRow + LastRow > 65536 Then
MsgBox "Cannot consolidate all data " _
& "as there are too many rows"
GoTo Endsub
End If
Sht.Range("A2:" & Lastcol & LastRow).Copy _
SummarySht.Range("A" & NewRow)
SummarySht.Range(SourceCol & NewRow & ":" _
& SourceCol & LastRow + NewRow - 1) = Sht.Name
NewRow = NewRow + LastRow - 1
End If

Next Sht
Endsub:
With SummarySht
Columns("A:" & SourceCol).EntireColumn.AutoFit
Range(SourceCol & "1") = "Source"
Rows("1:1").RowHeight = 35
Rows("1:1").VerticalAlignment = xlTop
Range("A2").Select
ActiveWindow.FreezePanes = True
Application.ScreenUpdating = True
End With
End Sub

 

[dbguy11]

I'm sure that would work fine but in my case I'm working with 2 different
files that are formatted the same for 18 different sources for 6 months each
for total of 206 files. I mention the number of files because not all of the
fields are labeled. I could do it manually but it would take forever and a
day. I used to have something either a macro or add in that would just
combine them all into one workbook into separate tabs.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/fso.htm

There is a macro example to copy a sheet

 

[Roger Govier]

Hi
Then take a look through the options that Ron de Bruin has at his site
http://www.rondebruin.nl/tips.htm

Scroll down to Copy/Paste/Merge examples