Consolidate data from multiple sheets

One of the common problems in managing data is bringing it all together. Let’s say we have some data scattered in multiple sheets that we want to bring it together in a single sheet. How would you do it?

One way is to copy it from multiple sheets and paste it at one location or the smarter was is to write a simple macro to do the same for us

Assume this data set

We have scattered data on 5 different sheets. Also note that the headers in the data are the same (that is a preferable situation)

Here is the macro that will consolidate the data

Sub combinedata()
Dim var As Integer
Dim sh As Worksheet
var = 0
For Each sh In Worksheets
 If sh.Name = 'Consolidated Data' Then
 var = 1
 Exit For
 End If
Next sh
If var = 0 Then Sheets.Add(Before:=Sheets(1)).Name = 'Consolidated Data' Else
Sheets('Consolidated Data').Move Before:=Sheets(1)
Sheets(2).Activate
Sheets(2).Range(Range('a1'), Range('A1').End(xlToRight)).Copy
Sheets(1).Activate
Sheets('Consolidated Data').Paste Destination:=Range('a1')
For Each sh In Worksheets
 If sh.Name <> ActiveSheet.Name Then
 With sh
 .Range('A2:N' & .Range('A' & Rows.Count).End(xlUp).Row).Copy _
 Range('A' & Range('A' & Rows.Count).End(xlUp).Row + 1)
 End With
 End If
Next sh
ActiveWindow.DisplayGridlines = False
Range('A1').CurrentRegion.Select
Selection.Columns.AutoFit
End Sub

>> Download the example workbook from down below <<

Follow the Steps

1. Copy this Code
2. Open the excel workbook where you want to consolidate data
3. Press the shortcut Alt + F11 to open the Visual Basic Window
4. In the Insert Menu, click on Module or use the shortcut Alt i m to add a Module. Module is the place where the code is written
5. In the blank module paste the code and close the Visual Basic Editor
6. Then use the shortcut Alt + F8 to open the Macro Box. You would have the list of all the macros here.
7. You would see the Macro that you have just pasted in the Module as 'CombineData'.
8. Run it. Instantly a new sheet (consolidated data) will be inserted and the Macro will automatically copy the data from the rest of the sheets

A few things to note

1. Be sure to enable macros before you start working with the code
2. This code is dynamic and can be used in your own workbook as well
3. The code will create a new sheet as Consolidated Data to consolidate the data. If you already have that sheet in your workbook, the data will be consolidated in that sheet
4. The blue and bold marked parts in the code can be changed to fit your data range

Some other handy Macros to make your life easier!

1. Automated Filter with Macro
2. Unhiding Multiple Sheets at Once
3. Covert Numbers into Indian Currency Words
4. Creating an Index of all Sheet names

Do you often consolidate data from multiple sheets? How do you do it currently? Share your experience with me!