James asks how he can combine data from all rows of all worksheets into a single worksheet. 11 Lines of VBA code will do the trick. Episode 636 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a great question sent in by James.
If you have a question for the podcast, please feel free to either call in and leave your question as a voice mail, or drop me a note bill@mrexcel.com.
James said hey, I have a workbook has 20 different worksheets, I need a way to take the data from all of those worksheets and combine them into a single worksheet.
Well, you know this is one that would be relatively hard to build with formula because you never know how many rows you're going to have and it's really easy to do with a Macro.
So, we said we're going to try and do some VBA once a week, today is the day.
I'm going to switch over to VBA and basically, it takes about 10 lines of code what we're gonna do is we're first going to add a brand new worksheet to this workbook, but we're not just going to use workbooks.add.
We're going to set that new worksheet equal to an object variable .
So, set WSN. That's my abbreviation for worksheet new equal to worksheets.add.
That's basically, going to add a new worksheet and give us a little variable name WSN, that will allow us to reference that worksheet.
I'm going to have a variable called NextRow.
I'm going to set that equal to one that tells me where I'm about to copy and we're going to loop through each worksheet in the workbook.
So, we'll say for each ws, ws is just a variable in this workbook .worksheets.
First thing if it's the sheet that we happen to be grabbing is WSN, we don't want to do anything.
So we say if not WSN.name equals ws.name, then how many rows do we have today? How many rows are on ws.
So, we say final row equals ws.cells.
Now, we want to start it either 65536 in Excel 2003 and before or 1048576 in Excel 2007.
So, I'm going to use rows.count comma 1 press the End key and then xlup .Row, that'll tell me how many rows I happen to have today assuming that I have data in column A.
Now, we're going to copy those rows.
So, we'll do ws.cells start in Row 1, Column 1 and we'll use a resize, .resize.
How many rows well, we'll use final Row.
How many columns while we'd have to know how many columns we have, let's say that we had 20 columns where the data /copy We can just put where the destination is the destination is WSN.cells.
Which row we want to copy to. Well, It's that variable next row and which column its call number one.
Finally, we have to make sure that next row gets larger each time so, we'll say next row equals next row plus final Row that way the second time through the loop it will write to the correct spot.
Finally, we have to end that if block, end if and then end the loop And that basically does it, that does our whole loop now at the end. I just want to do a few things I want to make sure that we activate that that new worksheet WSN.select, and maybe give them a quick little message, message box.
We could say final or next Row Minus 1 ampersand, that's the concatenation character rows combined.
There you go.
Simple little Macro if the comments weren't there be about 11 lines of text, we can go through and run that Macro.
Let's so, hit Tools, Macro, Macro and choose CombineAll, click Run.
Sure enough, 3384 rows combine.
Now, the one problem that we have here is the new sheet was not inserted at the beginning and we also get our headings appearing throughout the worksheet.
We probably should have added some code there and said hey is this the first time we're doing the copy if so start at Row 1, otherwise start at Row 2.
But you get the basic idea very simple little Macro that will solve this problem every time we run the Macro we get a brand new sheet that will copy all the other sheets, whether there's three sheets five sheets or 50 sheets.
Copy them into a single worksheet.
Thanks to James for send in that question and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a great question sent in by James.
If you have a question for the podcast, please feel free to either call in and leave your question as a voice mail, or drop me a note bill@mrexcel.com.
James said hey, I have a workbook has 20 different worksheets, I need a way to take the data from all of those worksheets and combine them into a single worksheet.
Well, you know this is one that would be relatively hard to build with formula because you never know how many rows you're going to have and it's really easy to do with a Macro.
So, we said we're going to try and do some VBA once a week, today is the day.
I'm going to switch over to VBA and basically, it takes about 10 lines of code what we're gonna do is we're first going to add a brand new worksheet to this workbook, but we're not just going to use workbooks.add.
We're going to set that new worksheet equal to an object variable .
So, set WSN. That's my abbreviation for worksheet new equal to worksheets.add.
That's basically, going to add a new worksheet and give us a little variable name WSN, that will allow us to reference that worksheet.
I'm going to have a variable called NextRow.
I'm going to set that equal to one that tells me where I'm about to copy and we're going to loop through each worksheet in the workbook.
So, we'll say for each ws, ws is just a variable in this workbook .worksheets.
First thing if it's the sheet that we happen to be grabbing is WSN, we don't want to do anything.
So we say if not WSN.name equals ws.name, then how many rows do we have today? How many rows are on ws.
So, we say final row equals ws.cells.
Now, we want to start it either 65536 in Excel 2003 and before or 1048576 in Excel 2007.
So, I'm going to use rows.count comma 1 press the End key and then xlup .Row, that'll tell me how many rows I happen to have today assuming that I have data in column A.
Now, we're going to copy those rows.
So, we'll do ws.cells start in Row 1, Column 1 and we'll use a resize, .resize.
How many rows well, we'll use final Row.
How many columns while we'd have to know how many columns we have, let's say that we had 20 columns where the data /copy We can just put where the destination is the destination is WSN.cells.
Which row we want to copy to. Well, It's that variable next row and which column its call number one.
Finally, we have to make sure that next row gets larger each time so, we'll say next row equals next row plus final Row that way the second time through the loop it will write to the correct spot.
Finally, we have to end that if block, end if and then end the loop And that basically does it, that does our whole loop now at the end. I just want to do a few things I want to make sure that we activate that that new worksheet WSN.select, and maybe give them a quick little message, message box.
We could say final or next Row Minus 1 ampersand, that's the concatenation character rows combined.
There you go.
Simple little Macro if the comments weren't there be about 11 lines of text, we can go through and run that Macro.
Let's so, hit Tools, Macro, Macro and choose CombineAll, click Run.
Sure enough, 3384 rows combine.
Now, the one problem that we have here is the new sheet was not inserted at the beginning and we also get our headings appearing throughout the worksheet.
We probably should have added some code there and said hey is this the first time we're doing the copy if so start at Row 1, otherwise start at Row 2.
But you get the basic idea very simple little Macro that will solve this problem every time we run the Macro we get a brand new sheet that will copy all the other sheets, whether there's three sheets five sheets or 50 sheets.
Copy them into a single worksheet.
Thanks to James for send in that question and thanks to you for stopping by.
See you next time for another netcast from MrExcel.
