Merge Multiple Single Worksheet Excel Files into one file in separ
 

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.

Answer: Merge Multiple Single Worksheet Excel Files into one file in separ
 

Hi there! Merging multiple single worksheet Excel files into one file while maintaining each worksheet into its own tab is a common task in Excel. Here's how you can do it step by step:

1. Open a new Excel workbook where you want to merge all the single sheeted workbooks.
2. Click on the "Insert" tab and select "Worksheet" to add a new worksheet.
3. Rename the new worksheet to something like "Merged Sheets".
4. Open the first single sheeted workbook that you want to merge.
5. Right-click on the worksheet tab and select "Move or Copy".
6. In the "Move or Copy" dialog box, select the "Merged Sheets" workbook from the "To book" drop-down list.
7. Check the "Create a copy" checkbox and click "OK".
8. Repeat steps 4-7 for all the single sheeted workbooks that you want to merge.
9. Once you have copied all the worksheets to the "Merged Sheets" workbook, you can rearrange them in any order you want by dragging and dropping the worksheet tabs.
10. Save the "Merged Sheets" workbook with a new name.

That's it! You have successfully merged multiple single worksheet Excel files into one file while maintaining each worksheet into its own tab.

Merge Multiple Single Worksheet Excel Files into one file in separ
 

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.

Merge Multiple Single Worksheet Excel Files into one file in s
 

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.

Merge Multiple Single Worksheet Excel Files into one file in separ
 

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

There is a macro example to copy a sheet


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"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.

Merge Multiple Single Worksheet Excel Files into one file in s
 

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.

[quote='Roger Govier[_3_];807742']Hi
Then take a look through the options that Ron de Bruin has at his site
http://www.rondebruin.nl/tips.htm

Hi

Ron's example is great and did everything I wanted apart from one thing:

I'd like to create a worksheet with formating and some other macros to manipulate the date already in place, then import all of the date from the other workbooks into this one.

I can combine all of the worksheets, but none of the formating comes across and it's all going into a new workbook. All attempts to edit Ron's code have just broken the macro completely :)

Is this a simple step, or is cutting and pasting the newly combined data the best way? I can do this, but fear what a mess my team mates may make!

Kind regards

FK