While yesterday's podcast was created with the macro recorder, you can switch over to the VBA editor to change 4 lines of the macro. This will create a macro that will work without requiring you to rename the pivot table. Episode 1028 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze, as well let's file up a pivot table.
Let's see if we solve this problem.
Back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday went through this big long hassle because I was going to solve the PivotTable problem, using our recorded macro.
That's where every time we created the pivot table.
We had to go into pivot table options and change the name to a specific name.
Now, I did that because I wanted the macro to work with the macro recorder.
If, you're not afraid to switch over in the VBA and do a little bit of coding to change that recorded macro.
We can avoid that whole hassle.
Here's what I'm going to do [ alt + F11 ], to go over and take a look here in the project explorer.
We double click on modules, double click on module one.
We get to see the recorded code and I'm going to add a few lines of code here to the top first line of code, dim PT.
That's a variable as pivot table and then I'm going to say, set PT = activesheet .pivot tables with an 'S', 1 and what that's going to do is it's going to set up a variable called PT.
Which refers to the first pivot table on the sheet.
In the example, I'm assuming that we only have one pivot table per sheet.
So, that's going to work out fine and then everywhere, where the recorded macro refers to active sheet dot pivot tables Bill PT.
I can simply play it in PT.
So, we'll go through here it looks like there are two of them PT and now we have a nice generic macro, that will always work with the first pivot table on the sheet.
We never have to go through and rename the sheets not even before we record the macro.
So, let's write here data pivot table [ finish ].
I'll put region on the left hand side and then maybe date next.
Finally, revenue profit and cost of goods sold going across.
Okay! So, now we can run that macro [ alt + F8 ] to get a list of macros.
Format my pivot, click [ run ] and when we go down to see the break between west and central.
It's successfully formatted that pivot table.
So, just a tiny bit of coding.
Basically, one two lines that I had type and then change the hard-coded pivot table names to refer to that variable.
Great way to make this very generic macro.
Hey! I want to thank you for stopping by of course.
You know, if you want to learn about VBA, I have a couple of products, the live lessons, power macros for Excel or even the books VBA and macros for Microsoft Excel.
Both designed to get you up to speed from recording macros to being able to generalize your macros with these few lines of code to make things much more powerful.
Alright hey!
I want to thank you for stopping by, we'll see you next time for another net cast, from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amount of data.
How we're gonna analyze, as well let's file up a pivot table.
Let's see if we solve this problem.
Back to the MrExcel netcast.
I'm Bill Jelen.
Yesterday went through this big long hassle because I was going to solve the PivotTable problem, using our recorded macro.
That's where every time we created the pivot table.
We had to go into pivot table options and change the name to a specific name.
Now, I did that because I wanted the macro to work with the macro recorder.
If, you're not afraid to switch over in the VBA and do a little bit of coding to change that recorded macro.
We can avoid that whole hassle.
Here's what I'm going to do [ alt + F11 ], to go over and take a look here in the project explorer.
We double click on modules, double click on module one.
We get to see the recorded code and I'm going to add a few lines of code here to the top first line of code, dim PT.
That's a variable as pivot table and then I'm going to say, set PT = activesheet .pivot tables with an 'S', 1 and what that's going to do is it's going to set up a variable called PT.
Which refers to the first pivot table on the sheet.
In the example, I'm assuming that we only have one pivot table per sheet.
So, that's going to work out fine and then everywhere, where the recorded macro refers to active sheet dot pivot tables Bill PT.
I can simply play it in PT.
So, we'll go through here it looks like there are two of them PT and now we have a nice generic macro, that will always work with the first pivot table on the sheet.
We never have to go through and rename the sheets not even before we record the macro.
So, let's write here data pivot table [ finish ].
I'll put region on the left hand side and then maybe date next.
Finally, revenue profit and cost of goods sold going across.
Okay! So, now we can run that macro [ alt + F8 ] to get a list of macros.
Format my pivot, click [ run ] and when we go down to see the break between west and central.
It's successfully formatted that pivot table.
So, just a tiny bit of coding.
Basically, one two lines that I had type and then change the hard-coded pivot table names to refer to that variable.
Great way to make this very generic macro.
Hey! I want to thank you for stopping by of course.
You know, if you want to learn about VBA, I have a couple of products, the live lessons, power macros for Excel or even the books VBA and macros for Microsoft Excel.
Both designed to get you up to speed from recording macros to being able to generalize your macros with these few lines of code to make things much more powerful.
Alright hey!
I want to thank you for stopping by, we'll see you next time for another net cast, from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast, from MrExcel.
