SmittyPro - Witticisms, Help, Tutorials and More

Excel: Automatically consolidate data from multiple worksheets?

written by "Smitty"

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

We had a question at #ExcelHelp this week from somone who wanted to consolidate data from multiple worksheets to a master sheet. My first thougt was use Excel’s native PivotTable functionality for multiple consolidations ranges (easier than it sounds, just follow the Wizard), but this case just needed to move an inconsistent range of worksheets just a few times, so VBA code seemed more appropriate for a one-off situation like this where you copy data from each worksheet to the master. The problem (especially if you’re just starting with VBA) is that one worksheet might have 52 rows of data and the next 73, or 1,008. So how do you deal with dynamic ranges like that? Unfortunately, this isn’t one that the Macro Recorder can take care of for you, so you need to go beyond that and actually start writing some code of your own, but this is where VBA’s power shines; you just need to know how to tap into it…

What might seem very complicated, really just needs a bit of thought and some dynamic ranging, so we can identify the range of data in each worksheet to be copied (A2:A1274 let’s say). But that just defines the copy from range, then we need to identify the first empty row in the worksheet where the data is going to be copied, and each time new rows of data are added, we need to find the new last row. Within all of that we’ll go through each worksheet and then redifine those ranges.

Here’s the resulting code, with comments so you can see what it’s doing:

Sub CopyfromSheets()
' Declare Variables for Worksheets & lr to get the last used row in a range
Dim ws As Worksheet
Dim lr As Long

' Loop through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
' Tell the code to ignore the "Master" worksheet
If ws.Name <> "Master" Then
' Find the last used row in column A in each sheet
lr = ws.Cells(Rows.Count, "A").End(xlUp).Row
' Rows.Count counts the number of rows in the worksheet and doesn't care if it's 65,536 or 1MM+.
' End(xlUp) goes from the last row at the bottom up to the last row at the top
' You can also use End(xlDown), but it will fall apart if you have blank rows in your data
' .Row gets the Row #, so "lr" can then be 52, 74, 1,008, etc., as it resets for each worksheet
' Copy/Paste - As long as you're not trying to Paste Special, like Values, then you can have Range.Copy -->Destination all on one line
' In the Paste part we use Offset(1) to move down to the next Empty row so you don't paste over existing data
ws.Range("A2:A" & lr).Copy Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Offset(1)
' Exit the IF statement evaluating the sheet name
End If
' Move on to the next worksheet. This doesn't care how many sheets you have, as it will go through them all
' If you only wanted to do this on 20 of 100 sheets you can define the For Each Next construct like this:
' For i = 10 to 30
' Sheets(i)...
' Next i
' Which goes from sheet #10 to sheet #30 and ignores any others
Next ws
End Sub

So now you have a way to dynamically copy data from multiple worksheets to a master sheet. And while this is a relatively simple example, it wouldn’t be hard to set it up to copy multiple columns. You could even set it up for a variable number of columns by adding the same methodology used to identify the last row:

lc = Cells(1,Columns.Count).End(xlToLeft).Column

Post to Twitter Post to Facebook Post to Google Buzz Post to LinkedIn Post to Technorati

One Response to “Excel: Automatically consolidate data from multiple worksheets?”

  1. namethi

    The syntax looks hard, but I will try it. Do you know Excel Aid? Maybe it inspires you for more great posts? See it yourself:

Leave a Comment - Here's your chance to speak.(eMail will not be published)

Separate individual tags by commas

Before you post, please prove you are sentient.

What is 3 * 7?

Microsoft MVP


Join In

Tag Cloud

Great Links


Welcome to, your one-stop for solutions concerning anything from beginner to extremely advanced Microsoft Excel issues and programming. Topics covered include Excel, Access, VBA, and (every now and then) some amazing observations or "Smitticisms" that might dumbfound you and send ripples of excitement or intrigue around the world.



RSS Top Stories from CNN

Vistor Counter for SmittyPro's Blog