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
--
Regards
Roger Govier
"dbguy11" wrote in message
...
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.
"Roger Govier" wrote:
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
--
Regards
Roger Govier
"dbguy11" wrote in message
...
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.