Leon runs a Macro that copies previously filtered records, per Worksheet, in his Workbook. This works great, but it presents a bit of a problem. The issue is that the Headings are also being copied - Leon does not need the headings each time the Macro runs and the data is copied. Follow along with Bill 'MrExcel' Jelen today, in Episode #1644 as he discerns the issue, institutes the necessary change and produces those copies without the Headings each time.
Learn More about VBA/Macros [Visual Basic for Applications] and how it can really ease your work-day! Look for Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Learn More about VBA/Macros [Visual Basic for Applications] and how it can really ease your work-day! Look for Bill's book, "VBA and Macros: Microsoft Excel 2010" by Bill Jelen and Tracy Syrstad. The fastest, best way to go beyond the Macro Recorder and move up the Excel VBA learning curve...Includes crucial information on making Excel 2010 VBA code work with older versions. VBA and Macros: Microsoft Excel 2010
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book! Power Excel With MrExcel - 2017 Edition
"The Learn Excel from MrExcel Podcast Series"
Visit us: MrExcel.com for all of your Microsoft Excel Needs!
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel from MrExcel podcast episode 1644: copy from each filtered worksheet.
Today's question is sent in by Leon.
Leon said that he has a macro and this macro is set up to copy the previously filtered records from each worksheet in a workbook, but the problem is that he's getting the headings in each one.
So, let's run the macro that I imagine that Leon has.
I will click run and sure enough, you see what what's happening is we're getting the headings from each of the worksheets.
Is there somewhere to not copy those headings?
So, let's just come here.
We're going to take a look at Monday and we're going to switch back and forth between VBA code.
I’m just going to use the immediate window down here at the bottom.
So, if I say tell me what the active cell dot address is, you'll see that I’m currently in A2.
So, from there take a look.
See it's just A2 that is selected.
If I say active cell dot current region dot select, you'll see that instead of just A2, we now have how many rows, how many columns.
You know, we have the whole data set, so, that's good.
Switch back to VBA and here is what I think we need to do.
Instead of saying active cell dot current region dot select, if we say active cell dot current region dot offset, one row down, zero columns to the left dot select, I think that will make sure that we select everything but the headings.
So, I'll press enter here, we’ll go back, look at that.
So, that moved my whole selection down one row.
All right, now, I can already hear what you're saying.
You're saying, “Oh wait, that's no good because you're going to get this blank row,” but I think that's okay because we're going to copy all of these records, including the blank row, to the summary worksheet.
Then, we're going to go to Tuesday and copy all of these records, including the blank row, to the summary sheet and we’ll overwrite the blank row from the previous time.
That's my goal.
All right, so, taking a look at the code that we have up here, for each worksheet in active workbook dot worksheets, make sure that this worksheet name is not equal to summary.
If that's true, figure out where the next row on the summary is going to be.
So, we start at the bottom of the summary and press the N key and the up arrow key, or control up arrow, see what row that is, plus 1.
Then, we start at-- actually we don't have to start at A2.
We can start at A1.
From A1, the current region dot offset, one comma zero, dot and then special cells selects only the visible cells.
The copy, this is a continuation character here, and the destination is going to be the summary worksheet, the next row, that's a variable, comma 1.
All right, so, with that one simple change, let's come back to our summary worksheet and we’ll do just Alt F8 to get a list, copy all, click run.
There we go.
So, we are getting everything without the headings.
Now, of course, we're not even getting the headings on the first one.
So, either you have to put those in manually or add one line to the macro, but I think to solve Leon's problem here, all we need to do is add the Offset.
Great little-- great little function in VBA; not as complicated as the OFFSET function in regular Excel.
This just says, hey, we're going to move one row down, zero columns over and it keeps that same shape of the selection, but moves it down.
Hey, I want to thank Leon for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast episode 1644: copy from each filtered worksheet.
Today's question is sent in by Leon.
Leon said that he has a macro and this macro is set up to copy the previously filtered records from each worksheet in a workbook, but the problem is that he's getting the headings in each one.
So, let's run the macro that I imagine that Leon has.
I will click run and sure enough, you see what what's happening is we're getting the headings from each of the worksheets.
Is there somewhere to not copy those headings?
So, let's just come here.
We're going to take a look at Monday and we're going to switch back and forth between VBA code.
I’m just going to use the immediate window down here at the bottom.
So, if I say tell me what the active cell dot address is, you'll see that I’m currently in A2.
So, from there take a look.
See it's just A2 that is selected.
If I say active cell dot current region dot select, you'll see that instead of just A2, we now have how many rows, how many columns.
You know, we have the whole data set, so, that's good.
Switch back to VBA and here is what I think we need to do.
Instead of saying active cell dot current region dot select, if we say active cell dot current region dot offset, one row down, zero columns to the left dot select, I think that will make sure that we select everything but the headings.
So, I'll press enter here, we’ll go back, look at that.
So, that moved my whole selection down one row.
All right, now, I can already hear what you're saying.
You're saying, “Oh wait, that's no good because you're going to get this blank row,” but I think that's okay because we're going to copy all of these records, including the blank row, to the summary worksheet.
Then, we're going to go to Tuesday and copy all of these records, including the blank row, to the summary sheet and we’ll overwrite the blank row from the previous time.
That's my goal.
All right, so, taking a look at the code that we have up here, for each worksheet in active workbook dot worksheets, make sure that this worksheet name is not equal to summary.
If that's true, figure out where the next row on the summary is going to be.
So, we start at the bottom of the summary and press the N key and the up arrow key, or control up arrow, see what row that is, plus 1.
Then, we start at-- actually we don't have to start at A2.
We can start at A1.
From A1, the current region dot offset, one comma zero, dot and then special cells selects only the visible cells.
The copy, this is a continuation character here, and the destination is going to be the summary worksheet, the next row, that's a variable, comma 1.
All right, so, with that one simple change, let's come back to our summary worksheet and we’ll do just Alt F8 to get a list, copy all, click run.
There we go.
So, we are getting everything without the headings.
Now, of course, we're not even getting the headings on the first one.
So, either you have to put those in manually or add one line to the macro, but I think to solve Leon's problem here, all we need to do is add the Offset.
Great little-- great little function in VBA; not as complicated as the OFFSET function in regular Excel.
This just says, hey, we're going to move one row down, zero columns over and it keeps that same shape of the selection, but moves it down.
Hey, I want to thank Leon for sending that question in.
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
