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
Follow the Steps
- Copy this Code
- Open the excel workbook where you want to consolidate data
- Press the shortcut Alt + F11 to open the Visual Basic Window
- 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
- In the blank module paste the code and close the Visual Basic Editor
- Then use the shortcut Alt + F8 to open the Macro Box. You would have the list of all the macros here.
- You would see the Macro that you have just pasted in the Module as ‘CombineData’.
- 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
- Be sure to enable macros before you start working with the code
- This code is dynamic and can be used in your own workbook as well
- 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
- 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!
- Automated Filter with Macro
- Unhiding Multiple Sheets at Once
- Covert Numbers into Indian Currency Words
- 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!