Consolidate Data

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

consolidate data 1

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).Range(Range("a1"), Range("A1").End(xlToRight)).Copy
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
End Sub

>> Download the example workbook here <<

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!


Topics that I write about...

Download Smart Ebooks on
Excel and Power BI