The Scenario
Let’s say that you have a lot of sheets in your workbook and you want to merge all the data onto a single worksheet.
If you have your data laid out in the same way on each sheet then this piece of VBA will do the trick for you. Maybe you have sales reports for different regions/products/salespeople on separate sheets, something like this :
Sheet 1
Sheet 2
The code allows for a header row (which is taken from the first sheet), and just copies the data off the remaining sheets.
Merging the sheets together will give you this :
Note : I used Excel's RANDBETWEEN function to generate the sales figures. I wasn't going to type out those numbers by hand, and if you need to generate random numbers too, try RANDBETWEEN.
The merged data is copied onto a sheet called MergedData. If you want to change this sheet name, just change the value between the double quotes of the MergeSheetName variable in the VBA as shown here :
When you run the code this is what happens :
- A new sheet called MergedData is created (or whatever you want to call it). If this sheet already exists then all data on it is deleted
- The header row and data from the first sheet in the workbook is copied to the merged data sheet
- The data from all other sheets is copied to the merged data sheet
- The header row on the merged results is made BOLD
- The columns on the merged results are auto-fitted
What to Expect from the VBA
There are certain things the code does not do and it’s important to understand these so you don’t end up with unexpected results
The range to be copied must be contiguous. I use a VBA property called CurrentRegion which copies a range bounded by blank rows/columns like so :
Our active cell is B2 so the range to copy is A2 to F4. CurrentRegion will select the range starting at A1, but I'm resizing the range in VBA to exclude the first row as we've already copied that from the first sheet. We only want the header row once.
Row 6 and Column H are ignored as Row 5 and Column G are blank – these are where CurrentRegion understands the range ends.
You can read Microsoft’s explanation of CurrentRegion here
This code cannot be used on a protected worksheet, CurrentRegion does not support this.
The code pastes values and formatting. So your merged sheet will not contain any formulas
Where’s the Code?
Enter your email address below to download the sample workbook.
Copy the code below into your own module, save this text file which contains the code or download the workbook and open the VBA editor (ALT+F11) to check out the code and start merging.
Option Explicit
Sub MergeSheets()
' Author - Philip Treacy
' https://www.myonlinetraininghub.com/merge-excel-worksheets-with-vba
' Date - 30 Sep 2013
' Merge all sheets in a workbook into one summary sheet
Dim MergeSheet As Worksheet, wSheet As Worksheet, tempSheet As Worksheet
Dim NumRows As Long, StartRow As Long
Dim FirstSheet As Boolean
Dim MergeSheetName As String
Dim copyRange As Range
MergeSheetName = "MergedData"
Application.ScreenUpdating = False
'Add sheet for merged data if it doesn't exist
On Error Resume Next
Set tempSheet = Sheets(MergeSheetName)
If tempSheet Is Nothing Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MergeSheetName
End If
'Setup
Set MergeSheet = ActiveWorkbook.Sheets(MergeSheetName)
MergeSheet.Cells.ClearContents
FirstSheet = True
StartRow = 2
'Process each data sheet
For Each wSheet In Worksheets
'If we are on the sheet where we are copying the merged data to, skip it
If wSheet.Name <> MergeSheetName Then
'Calcuate how many rows of data to copy from the sheet
NumRows = wSheet.Range("A" & wSheet.Rows.Count).End(xlUp).Row
'Copy header row from first sheet
If FirstSheet Then
wSheet.Range("A1", wSheet.Cells(1, Columns.Count).End(xlToLeft)).Copy
MergeSheet.Range("A1").PasteSpecial xlPasteAll
FirstSheet = False
End If
'Activate sheet to be copied and select the range of data for copying
wSheet.Activate
wSheet.Range("A2").Select
Set copyRange = ActiveCell.CurrentRegion
'Resize the range to exclude the header row, but copy all other data
copyRange.Offset(1, 0).Resize(copyRange.Rows.Count - 1, copyRange.Columns.Count).Copy
'Paste values and formatting
MergeSheet.Range("A" & StartRow).PasteSpecial xlPasteValuesAndNumberFormats
'Set StartRow which is where the next lot of data will be pasted into
StartRow = MergeSheet.Range("A" & MergeSheet.Rows.Count).End(xlUp).Row + 1
End If
Next wSheet
'Tidy Up
MergeSheet.Rows(1).Font.Bold = True
MergeSheet.Cells.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
MergeSheet.Activate
Range("A1").Select
Set MergeSheet = Nothing
Set tempSheet = Nothing
End Sub
What do you think?
If you need a hand adapting this to do something else for you please let me know.
This code was written as a result of a question from one of our students, Anna Reifman, thanks Anna.
I’d like to hear from you if you have a problem you think VBA could fix, or if you have your own solution to merging sheets, or even if you have adapted my code to do something else.
Please share
I'd be very grateful if you let others know about this using your favourite network. Just click the icons below.
Russ De Rosayro says
Hi Phil or Mernda or Catalin
OK, have tried unsuccessfully to post a query in the General section as per Catalin’s instructions / replies on this page. I need to merge several worksheets in a workbook into one sheet. All my worksheets seem to have hidden or blank rows, with some merged cells spanning two rows, and start in column B, Row 4..This was a result of converting/downloading bank statement data in .xls format from my bank.
I can use Phil’s vba code for merging worksheets but the merged data fails due to the structure of my worksheets.
If I delete all the blank columns and rows in each of the worksheets everything works fine but…. I have between 25 and 30 worksheets in each file and I have approx 20 files. Phew!
Could you please tell me how to post correctly as I am having no luck with posting from this page. Catalin requested I post in the General Section, as per her replies to me on this, but am having no luck so far.
I could communicate vie email and attach my sample file…rde12621@bigpond.net.au
Thanks
Russ
Catalin Bombea says
Hi Russ,
I created a new topic here: merge-several-worksheets-in-a-workbook-into-one-sheet
The topic is in the Public Forums – For Registered Users
Anyone who has registered on the site can post in these forums, so make sure you create your account on our forum. Once you registered, you will be able to create topics, upload files. To add a reply to an existing topic, just use the button Add Reply. Of course, you have to be logged in to add replies.
Catalin
Russ De Rosayro says
Hi Philip
I have previously subscribed to the Dashboard & Pivot Tables courses and used Excel daily with these skills I picked up, but have now retired for over a year.
The old adage holds true…use it or lose it and I am afraid I have lost some of my Excel VBA skills (not that I had that great a range!).
I downloaded the code for merging several bank statement worksheets into one sheet in an Excel workbook but find when I run it on my file it causes issues. For starters, I can see that my A column is blank but I can compensate by deleting my A column, since my data starts in the B column and row 2. However, I have at least 20 workbooks I want to run this on and each workbook contains up to 30 sheets so manually deleting every blank column A is quite tedious, even if I select all sheets and do a global delete in my file. Can you please help? I can forward the file but did not see how to attach in this reply.
Thanks
Russ
Catalin Bombea says
Hi Russ,
You can upload a file on our forum (create a new topic and add as many details you have)
We’ll help you fix it.
Catalin
Russ De Rosayro says
Hi Catalin
Thanks.
I have uploaded a sample file after going to the Forum link but am not sure of exactly which forum I joined, so how/where do I see the replies?
Russ
Catalin Bombea says
Hi Russ,
Not seeing any topic related to your subject on forum, maybe you did not completed it properly. Can you try again, in General section? You can configure your account to receive emails for subscribed topics, when a new reply is added.
Catalin
Russ De Rosayro says
Hi Catalin
OK, posted my detailed problem(s)/queries in General Section but no reply yet?
What am I doing wrong?
Catalin Bombea says
Can you send the link to your new topic? There is no new topic on forum, I believe you did not created one. You will have to try again.
Catalin
Pad says
Hey! Great example!! Could you please help me, I am trying to put a button on the spreadhseet that I can click that would do this, instead of running it from the VBA
Thanks
Catalin Bombea says
Hi Pad,
You can simply insert any shapes, then right click the shape and click on Assign Macro, then select your code from the list.
Or, from Developer tab, Insert>Button, then assign macro.
Lynne Polk says
How do you get this to work on word documents in a folder. I need to copy all the word documents in a folder to an excel spreadsheet. The word document has a table at the top and free form text at the bottom.
Thanks,
Lynne
Philip Treacy says
Hi Lynne,
Its a bit difficult to say exactly without seeing the Word docs and an example of how you want the Excel sheets to look after importing the Word docs. For example, the ‘free form’ text, do you want that imported into a single cell in the Excel sheet, or do you want each line inserted into a different row? Do you want the text wrapped in the cell(s)? What width do you want the cells?
If you can open a Helpdesk ticket and attach some of the Word docs, with an example worksheet of how you want them to look after importing into Excel, we can give you an idea of what solution would work best.
If you don’t have many Word docs you may be better off doing the job by hand rather than writing code.
Regards
Phil
Ozz says
Hi everyone,
First of all I have to tell that I have no experience with Macro (VBA Codes). However what I need is related to this. Maybe you guys could help me with it.
I have a workbook and in this workbook there are 10 worksheets. The first 9 Sheets have the same order of the coloumns of titles and in these columns there are names, dates, percentages of Project Status, comments to Projects etc.. As I said the columns have the same order just the name of the worksheets (for different Teams in the Organisation) are different.
In Addition to this I have to merge all the worksheets and have them in another sheet which is called “Übersicht” (Overview). However there is a different column in the sheet and it’s between “Nr.” and “Thema” columns (which are in A1 and A2 in all the 9 Sheets) and this different column called “Kategorie” (in A2 in Übersicht-Overwiev sheet). As this column is between These the order is like this “Nr. (A1), Kategorie (A2) and Thema (A3)…..”.So this category column (Kategorie) should be empty except this all the Information should be merged into this sheet. And also when there is a Change or update in any worksheet, the Information in “Übersicht” (Overview) sheet needs to update by itself. How can I do this?
P.S.: Every sheet has different filled rows, some 30, some 13, some 5 etc. And the Teams which are responsible for the Sheets can add or delete some rows (in each row there is different Information for different Projects). This also means the number of rows can increase or decrease.
I hope I explained it well. Thanks a lot in advance!
I wish you merry Christmas and a happy new year!
oduff
Catalin Bombea says
Hi Ozz,
I think you should open a new topic on our forum, and upload a sample file.
The following code will merge data in a more flexible way, the columns may not be in the same order as in the first sheet:
Option Explicit
Sub MergeSheets()
' Author - Philip Treacy
' http://www.myonlinetraininghub.com/merge-excel-worksheets-with-vba
' Date - 30 Sep 2013
' Merge all sheets in a workbook into one summary sheet
Dim MergeSheet As Worksheet, wSheet As Worksheet, tempSheet As Worksheet
Dim NumRows As Long, StartRow As Long
Dim FirstSheet As Boolean
Dim MergeSheetName As String
Dim DataItem As Variant, HeadersRow As Range, i As Long, HeaderText As String
Dim DataDict As Object: Set DataDict = CreateObject("Scripting.Dictionary")
Dim HeadersDict As Object: Set HeadersDict = CreateObject("Scripting.Dictionary")
MergeSheetName = "MergedData"
Application.ScreenUpdating = False
'Add sheet for merged data if it doesn't exist
On Error Resume Next
Set tempSheet = ThisWorkbook.Worksheets(MergeSheetName)
If tempSheet Is Nothing Then
Worksheets.Add(After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)).Name = MergeSheetName
End If
'Setup
Set MergeSheet = ThisWorkbook.Worksheets(MergeSheetName)
MergeSheet.UsedRange.ClearContents
FirstSheet = True
StartRow = 2
'collect data from each sheet
For Each wSheet In Worksheets
If wSheet.Name <> MergeSheetName Then
DataDict.Add Key:=wSheet.Name, Item:=wSheet.Cells(1).CurrentRegion
End If
Next wSheet
'loop through data and write to destination
For Each DataItem In DataDict.Keys
If FirstSheet Then
DataDict(DataItem).Copy Destination:=MergeSheet.Range("A1")
'load headers position
For i = 1 To DataDict(DataItem).Rows(1).Cells.Count
HeadersDict.Add Key:=DataDict(DataItem).Rows(1).Cells(i).Text, Item:=DataDict(DataItem).Rows(1).Cells(i).Column
Next
FirstSheet = False
Else
StartRow = MergeSheet.Range("A" & MergeSheet.Rows.Count).End(xlUp).Row + 1
'paste data based on each header position
For i = 1 To DataDict(DataItem).Rows(1).Cells.Count
' if we have this header in first sheet, copy the data
HeaderText = DataDict(DataItem).Rows(1).Cells(i).Text
If HeadersDict.Exists(HeaderText) Then
DataDict(DataItem).Columns(i).Offset(1, 0).Resize(DataDict(DataItem).Rows.Count - 1, 1).Copy _
Destination:=MergeSheet.Cells(StartRow, HeadersDict(HeaderText))
End If
Next
End If
Next DataItem
'Tidy Up
MergeSheet.UsedRange.Rows(1).Font.Bold = True
MergeSheet.UsedRange.Cells.Columns.AutoFit
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.Goto MergeSheet.Range("A1")
Set MergeSheet = Nothing
Set tempSheet = Nothing
Set DataDict = Nothing
Set HeadersDict = Nothing
End Sub
You can use the Worksheet_Activate event, whenever you open the Merged Data sheet, the code that collects data should be called.
See you on forum, with a sample file.
Cheers,
Catalin
Lynne Polk says
This is great! Thanks for sharing.
Philip Treacy says
Thanks 🙂
Phil
Kitty says
OMG this made my life so much easier. Thank you!!
Philip Treacy says
Glad it was useful 🙂
Regards
Phil
David says
Beautiful! What would you need to change to have it ignore the first sheet and continue as normal. In other words, I have a “Raw Data” sheet that I’m parsing bits of to Sheet2, Sheet3, etc… then I would like to have those Sheets2, 3, etc. re-combined into the “Merged Data” sheet excluding the “Raw Data” sheet.
Does that make sense? Thanks for the great walk-through!
Catalin Bombea says
Hi David,
You can set a list of sheets to avoid… like this:
(in this example, the code will avoid Sheet1 and Sheet2)
For each Wks in Thisworkbook.WorksheetsIf Instr("Sheet1, Sheet2",Wks.Name)=0 Then
'then this sheet is not in NoSheetsList, can be processed...
'add here your code
End If
Next Wks
Catalin
Dominic says
Thanks for sharing the vba (downloaded file) to merge the worksheets, it works a treat. But I would also like to incorporate the additional vba to avoid a set of sheets (ie Sheets 1 and Sheets 2 etc). Could you provide the complete vba needed to incorporate the vba in the downloaded file with the code provided in the response to David’s query? I am a novice with excel vba and would like to simply paste the complete vba code into my module?
Thanks in advance
Catalin Bombea says
Hi Dominic,
You will see that it’s a very simple change to make.
In the downloaded workbook, in the vb module, look at line 35:
For Each wSheet In WorksheetsAdd a new line below that line:
If Instr("Sheet1, Sheet2",WSheet.Name)=0 ThenYou have to edit the string “Sheet1, Sheet2” to type the names of the sheets you want to avoid.
Now, look at row 69: (in the original code, now you have added a new line, it should be in row 70)
Next wSheetAdd this text above that line:
End IfIt should be very intuitive to add new lines of code, it’s similar to editing a text file.
Let me know if you managed to do it.
Cheers,
Catalin
Dominic says
Hi Catalin,
Yes, I got it and works a treat, thanks and much appreciated!
Regards
Catalin Bombea says
Great, glad to hear you made it work 🙂
Elinor says
good day,
kindly help me how to use this code, i dont know how to apply this code.
i dont know how to insert the new code if i want to escape some sheets.
Because i have three worksheets with the same format and contents and i want to combined those worksheets in just one sheet. so please kindly help me how to do it….
thank you very much and God bless!
Catalin Bombea says
Hi Elinor,
Please open a new topic on our forum, and upload a sample file, we will help you write the codes into your file. Make sure you don’t upload sensitive data.
Catalin
Cathy-Lynn Hanson says
I am interested in knowing how this is extended to work with a group of workbooks instead of worksheets as Juanito asked. Would you share the steps with me also?
Thanks
Catalin Bombea says
Hi Cathy,
Here is a code, with comments that makes things easier to understand.
You have to place all workbooks in the same folder, then change the path in code to point to this folder. Also, you can change the range you want to copy.
Sub MergeAllWorkbooks()Dim SummarySheet As Worksheet
Dim FolderPath As String
Dim NRow As Long
Dim FileName As String
Dim WorkBk As Workbook
Dim SourceRange As Range
Dim DestRange As Range
' Create a new workbook and set a variable to the first sheet.
Set SummarySheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
' Modify this folder path to point to the files you want to use.
FolderPath = "C:\Users\Peter\invoices\"
' NRow keeps track of where to insert new rows in the destination workbook.
NRow = 1
' Call Dir the first time, pointing it to all Excel files in the folder path.
FileName = Dir(FolderPath & "*.xl*")
' Loop until Dir returns an empty string.
Do While FileName <> ""
' Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName)
' Set the cell in column A to be the file name.
SummarySheet.Range("A" & NRow).Value = FileName
' Set the source range to be A9 through C9.
' Modify this range for your workbooks.
' It can span multiple rows.
Set SourceRange = WorkBk.Worksheets(1).Range("A9:C9")
' Set the destination range to start at column B and
' be the same size as the source range.
Set DestRange = SummarySheet.Range("B" & NRow)
Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
SourceRange.Columns.Count)
' Copy over the values from the source to the destination.
DestRange.Value = SourceRange.Value
' Increase NRow so that we know where to copy data next.
NRow = NRow + DestRange.Rows.Count
' Close the source workbook without saving changes.
WorkBk.Close savechanges:=False
' Use Dir to get the next file name.
FileName = Dir()
Loop
' Call AutoFit on the destination sheet so that all
' data is readable.
SummarySheet.Columns.AutoFit
End Sub
Maneesh Massey says
This looks GREAT !! Only thing I need is to consolidate data using Non-contiguous columns. Can you help me with this please ???
Thanks in advance.
~Regards,
Maneesh
Philip Treacy says
Hi Maneesh,
Can you send an example workbook to us with a detailed explanation, via the Help Desk so we can see exactly what it is you want to do.
Regards
Phil
MF says
Hi – thanks for sharing the code. I think many people encounter this problem and need the codes.
Btw, I am thinking about a non-VBA way “Data Consolidate (sum)” for those who are not comfortable with VBA to achieve the above (provided that there is no duplication of name in different worksheets; and the table structure is just simple as that). I try that and it works.
Do you think it’s a good alternative?
Mynda Treacy says
Hi MF,
The Consolidate tool is occasionally useful, but more often than not I find it doesn’t do exactly what I want. For example, if more than one column contains text it doesn’t bring all of the text in – only the top row and left column.
It’s a shame because it appears to do so much but in reality it does so little!
Cheers,
Mynda.
MF says
Hi Mynda,
I agree with you. It “looks” great however there is not many real-life application.
One way to solve the problem of more than one columns is to concatenate all columns before consolidation, then “text to columns” after the consolidation. BUT This is too troublesome and doesn’t sound like a good solution.
I am just wondering if there is any real-life application for the “Consolidate” as far as i know not many people talk about it.
Cheers,
MF 🙂
Mynda Treacy says
Hi MF,
I think the clue is in the fact that not many people talk about it…that’s because it’s farily useless in the real world. Although your workaround is good for those who prefer not to use VBA….although I’m not sure it’d be much faster than copy and paste.
Kind regards,
Mynda.
juanito says
Hi – just last week I started looking for ways to do this (turned out to be a lot less elementary than I’d expected), so this has really come in handy. Thanks!
Could this be extended to work with a group of workbooks instead of worksheets?
– juanito
Philip Treacy says
Hi Juanito,
You’re welcome.
Yes you could extend this to workbooks. Would you want to email the entire workbook(s), or sheets from within those workbooks?
regards
Phil
Bryan says
Looks good.
The only thing it’s desperately missing is a function that emails the originator of the workbook to chastise them for poor data structuring 😉
Philip Treacy says
LOL Bryan 🙂 Making things look pretty isn’t my forte