Eric writes in with a frustrating question; "Why can't the macro recorder record the action of creating a pivot table? No matter what I do, it hardcodes that the original pivot data is 469 rows tall." Eric doesn't want to learn VBA, but needs enough to be able to generalize the recorded code. Episode 1211 shows you a solution.
Transcript of the video:
The MrExcel Podcast is brought to you by Easy-XL.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
It's Memorial Day; no work today the United States.
Great thing, hope most of you are home, enjoying the picnic with your family.
Today's question’s sent in by Eric.
I think Eric's question is going to lead to a whole new segment on the Podcast.
Eric says, “I want to create a macro that will make a Pivot Table out of this data”.
He says, “I understand you guys at MrExcel, you're all about VBA.
All ten thousand questions a year at the message board are VBA questions; you have those VBA classes; you sell the live lessons from Q that teaches people how to do VBA.
I don't want to learn VBA,” he says.
“I just want to know enough to make the macro recorder work.
No matter what I do with Relative, or not Relative, the macro recorder hard codes the fact that when I created the Pivot Table, I had 469 rows of data, and then if I try and run it on a different data set that has more or less rows of data, it only uses the first 469 rows.
How can I take that recorded macro and generalize it so it works with any amount of data?” Alright, so, that's our goal today: I'm not going to teach you VBA; I'm just going to show you how to fix the macro recorder.
Alright.
So, we want to go to View, you want to make sure that Relative is turned on.
Now, it's turned on here because I have a gold glow.
By default it's not going to turn on on your machine, so you're going to want to click Use Relative References.
Relative References should be turned on all the time; why they didn't make it a default I have no idea-- so make sure that you have a gold glow there.
And then we're going to Record a Macro, call it CreatePivot; normally it's stored in a personal macro workbook, for this Podcast, though, I'm storing it in this workbook.
Alright.
The macro recorder is working.
So, Insert, PivotTable, click OK.
Let's put Products down the left hand side; Revenue in the heart of the Pivot Table; Region going across.
Good.
Let's take a look at our Pivot Table over here.
So you see it looks like we want it to look.
Then we go down to the bottom left-hand corner of the screen, down here where we have the Stop button.
Okay, now, as Eric pointed out, that macro is going to only work on 469 rows data every single time.
So, to see the macro, you can use the Developer tab of the ribbon; that's turned off by default, I'm going to use the shortcut key of Alt+F11.
If this is your first time in the macro recorder, Ctrl+R to show the Project window up here and then you might have to expand your workbook; you'll definitely have to expand Modules, and that's where they record the code-- double-click there.
Alright.
Now, a couple of things we're going to see: Right here, R469-- that's where they hard-coded how many rows of data were there on the day that it was recorded, so I need to generalize that piece.
I'm going to add a new variable in, the variable is called FinalRow, and this line of code is in every macro that I write-- you're just going to have to memorize this piece-- FinalRow Cells(Rows.Count, 1) .End(x1Up) .Row FinalRow.
Now, FinalRow, there's nothing magic about that-- you can call it FR, you can call it X, I don't care-- I use FinalRow because it makes sense to me.
And it basically goes down to the last cell in the spreadsheet-- either A65536 if we're in Compatibility mode, or A104576.
If you're not in Compatibility mode-- presses the “N” key and the Up Arrow key, which takes me to the last row of the data.
And it remembers which row that is.
Here, where they've hard-coded 469, I'm going to put quote, ampersand, my variable name FinalRow, another ampersand, and a quote, get rid of the 469-- R” & FinalRow & “C8” Okay, so that is enough to generalize things that it will use a different number of rows.
But there's two other things that they've hard-coded: They've hard-coded the fact that the Pivot Table went to a new worksheet and that new worksheet happens to be called Sheet 10.
We can't always assume that's correct because every time you do Sheets.Add, it’s going to use a different sheet name.
So I'm going to call it NewSheet = ActiveSheet.Name.
That's another variable.
And before they do Sheets.Add, I'm going to figure out the DataSheet = ActiveSheet.Name.
So I created three variables here, I have to now just use those variables in the right spot.
So the source data is on the DataSheet, so we use DataSheet ampersand (DataSheet &) and I can get rid of the Today.
Good.
Later on here, they create a Pivot Table and they put it on NewSheet-- so use that variable, ampersand, get rid of Sheet10 (NewSheet & “!RC31”).
And then later on, they select Sheet10, so I'm going to get rid of the quotes there and say Sheets(NewSheet).Select.
Alright, so three things-- three things-- that I had to change.
Basically add FinalRow as a variable, DataSheet as a variable, NewSheet as a variable, and then put those in the first few lines of code.
Funny, after that, everything else that gets recorded is fine.
I don't have to touch all the rest of it, it's just those opening bits that we have to record.
Okay, so come back here, choose our worksheet, we'll go to Tomorrow-- Tomorrow with 564 rows.
Let's just do a little test here: Ctrl+Shift+Up Arrow.
Select all the Revenue cells down here in the lower right-hand corner; looks like we have 6.7 million, so that's our goal.
We want to create a Pivot Table, should have 6.7 million; press Alt+F8; Create Pivot; click Run, and there we go--6.7 million.
So, turn on the macro recorder; three variables that we added; and then, very carefully, concatenated those variables in, where the macro recorder had hard-coded the number of rows and the name of the new sheet.
So, Eric, great question.
Hopefully that's enough to get your Pivot Table macro running.
For everyone else, I understand a lot of people are intimidated by VBA, don't want to understand VBA, but just want enough-- those little bits that they can add to the recorded macro to make life work.
In this particular case, it did.
So, hey, I want to thank you for stopping by, we'll see you next time for another for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
It's Memorial Day; no work today the United States.
Great thing, hope most of you are home, enjoying the picnic with your family.
Today's question’s sent in by Eric.
I think Eric's question is going to lead to a whole new segment on the Podcast.
Eric says, “I want to create a macro that will make a Pivot Table out of this data”.
He says, “I understand you guys at MrExcel, you're all about VBA.
All ten thousand questions a year at the message board are VBA questions; you have those VBA classes; you sell the live lessons from Q that teaches people how to do VBA.
I don't want to learn VBA,” he says.
“I just want to know enough to make the macro recorder work.
No matter what I do with Relative, or not Relative, the macro recorder hard codes the fact that when I created the Pivot Table, I had 469 rows of data, and then if I try and run it on a different data set that has more or less rows of data, it only uses the first 469 rows.
How can I take that recorded macro and generalize it so it works with any amount of data?” Alright, so, that's our goal today: I'm not going to teach you VBA; I'm just going to show you how to fix the macro recorder.
Alright.
So, we want to go to View, you want to make sure that Relative is turned on.
Now, it's turned on here because I have a gold glow.
By default it's not going to turn on on your machine, so you're going to want to click Use Relative References.
Relative References should be turned on all the time; why they didn't make it a default I have no idea-- so make sure that you have a gold glow there.
And then we're going to Record a Macro, call it CreatePivot; normally it's stored in a personal macro workbook, for this Podcast, though, I'm storing it in this workbook.
Alright.
The macro recorder is working.
So, Insert, PivotTable, click OK.
Let's put Products down the left hand side; Revenue in the heart of the Pivot Table; Region going across.
Good.
Let's take a look at our Pivot Table over here.
So you see it looks like we want it to look.
Then we go down to the bottom left-hand corner of the screen, down here where we have the Stop button.
Okay, now, as Eric pointed out, that macro is going to only work on 469 rows data every single time.
So, to see the macro, you can use the Developer tab of the ribbon; that's turned off by default, I'm going to use the shortcut key of Alt+F11.
If this is your first time in the macro recorder, Ctrl+R to show the Project window up here and then you might have to expand your workbook; you'll definitely have to expand Modules, and that's where they record the code-- double-click there.
Alright.
Now, a couple of things we're going to see: Right here, R469-- that's where they hard-coded how many rows of data were there on the day that it was recorded, so I need to generalize that piece.
I'm going to add a new variable in, the variable is called FinalRow, and this line of code is in every macro that I write-- you're just going to have to memorize this piece-- FinalRow Cells(Rows.Count, 1) .End(x1Up) .Row FinalRow.
Now, FinalRow, there's nothing magic about that-- you can call it FR, you can call it X, I don't care-- I use FinalRow because it makes sense to me.
And it basically goes down to the last cell in the spreadsheet-- either A65536 if we're in Compatibility mode, or A104576.
If you're not in Compatibility mode-- presses the “N” key and the Up Arrow key, which takes me to the last row of the data.
And it remembers which row that is.
Here, where they've hard-coded 469, I'm going to put quote, ampersand, my variable name FinalRow, another ampersand, and a quote, get rid of the 469-- R” & FinalRow & “C8” Okay, so that is enough to generalize things that it will use a different number of rows.
But there's two other things that they've hard-coded: They've hard-coded the fact that the Pivot Table went to a new worksheet and that new worksheet happens to be called Sheet 10.
We can't always assume that's correct because every time you do Sheets.Add, it’s going to use a different sheet name.
So I'm going to call it NewSheet = ActiveSheet.Name.
That's another variable.
And before they do Sheets.Add, I'm going to figure out the DataSheet = ActiveSheet.Name.
So I created three variables here, I have to now just use those variables in the right spot.
So the source data is on the DataSheet, so we use DataSheet ampersand (DataSheet &) and I can get rid of the Today.
Good.
Later on here, they create a Pivot Table and they put it on NewSheet-- so use that variable, ampersand, get rid of Sheet10 (NewSheet & “!RC31”).
And then later on, they select Sheet10, so I'm going to get rid of the quotes there and say Sheets(NewSheet).Select.
Alright, so three things-- three things-- that I had to change.
Basically add FinalRow as a variable, DataSheet as a variable, NewSheet as a variable, and then put those in the first few lines of code.
Funny, after that, everything else that gets recorded is fine.
I don't have to touch all the rest of it, it's just those opening bits that we have to record.
Okay, so come back here, choose our worksheet, we'll go to Tomorrow-- Tomorrow with 564 rows.
Let's just do a little test here: Ctrl+Shift+Up Arrow.
Select all the Revenue cells down here in the lower right-hand corner; looks like we have 6.7 million, so that's our goal.
We want to create a Pivot Table, should have 6.7 million; press Alt+F8; Create Pivot; click Run, and there we go--6.7 million.
So, turn on the macro recorder; three variables that we added; and then, very carefully, concatenated those variables in, where the macro recorder had hard-coded the number of rows and the name of the new sheet.
So, Eric, great question.
Hopefully that's enough to get your Pivot Table macro running.
For everyone else, I understand a lot of people are intimidated by VBA, don't want to understand VBA, but just want enough-- those little bits that they can add to the recorded macro to make life work.
In this particular case, it did.
So, hey, I want to thank you for stopping by, we'll see you next time for another for another netcast from MrExcel.
