Use VBA to group a pivot table date field up by weeks
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Excel VBA Chapter 13: Group by Week.
Well hey welcome back to the MrExcel netcast.
I'm Bill Jelen working through the VBA book chapter 13 is all about PivotTables, which are my favourite feature in Excel.
I love to do PivotTables in VBA.
The example that I chose for the podcast is one where we need to take daily dates and roll them up.
Okay, now this is strange because this is the one example where we have to let the PivotTable draw before we can go on.
So normally we make all the changes of the PivotTable and then at the end refresh the PivotTable but when you're grouping daily dates you need to let the PivotTable draw.
So we can select either the heading or the first date field, either one.
In essence what the macro is going to do is it's going to go to this dialog box and notice, seconds, minutes, hours days, months, quarters and years.
Helps if you can remember the sequence of that because you're going to have to be able to pass a series of ‘false(s)’ and ‘trues’ based on what you chose and in the macro what I'm going to do is I'm going to say only days.
That will be the only one chosen and then use this spin button to roll things up to seven-day periods.
Okay, so that's what we're trying to do in the macro.
Let's go take a look at the code and here we are.
We run the macro down to this point and this is where we check and see what the first date is and then do a little dance here to figure out, you know what the previous Monday is but once we have that then we're actually going to use this group method.
The group method; the curious thing about the group method is the periods field which is an array and again it goes seconds, minutes, hours, days, months, quarters and years so you have to pass a series of false(s) and trues in order and so you can see here that I'm using only the trues.
Now only the days are true.
Now, once you've done that then the cool thing is this argument the ‘By’ argument becomes enabled so I can say, “hey roll this up to seven-day periods” and I pass a start date to make sure that that start date is always a Monday.
Run this little bit of code here you'll see that the start date is now whatever 4/05/06 is on that code.
Now let's go take a look and compare the PivotTable to how it looked at the beginning.
Initially we had daily dates going down here and now we have weekly dates going down the left-hand side.
So if you work in payroll or a manufacturing plant or anything where you need to roll things up to weekly dates, a great little trick there to choose only days.
Only days from the Grouping button, grouping dialog and then roll it up to seven-day period.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Excel VBA Chapter 13: Group by Week.
Well hey welcome back to the MrExcel netcast.
I'm Bill Jelen working through the VBA book chapter 13 is all about PivotTables, which are my favourite feature in Excel.
I love to do PivotTables in VBA.
The example that I chose for the podcast is one where we need to take daily dates and roll them up.
Okay, now this is strange because this is the one example where we have to let the PivotTable draw before we can go on.
So normally we make all the changes of the PivotTable and then at the end refresh the PivotTable but when you're grouping daily dates you need to let the PivotTable draw.
So we can select either the heading or the first date field, either one.
In essence what the macro is going to do is it's going to go to this dialog box and notice, seconds, minutes, hours days, months, quarters and years.
Helps if you can remember the sequence of that because you're going to have to be able to pass a series of ‘false(s)’ and ‘trues’ based on what you chose and in the macro what I'm going to do is I'm going to say only days.
That will be the only one chosen and then use this spin button to roll things up to seven-day periods.
Okay, so that's what we're trying to do in the macro.
Let's go take a look at the code and here we are.
We run the macro down to this point and this is where we check and see what the first date is and then do a little dance here to figure out, you know what the previous Monday is but once we have that then we're actually going to use this group method.
The group method; the curious thing about the group method is the periods field which is an array and again it goes seconds, minutes, hours, days, months, quarters and years so you have to pass a series of false(s) and trues in order and so you can see here that I'm using only the trues.
Now only the days are true.
Now, once you've done that then the cool thing is this argument the ‘By’ argument becomes enabled so I can say, “hey roll this up to seven-day periods” and I pass a start date to make sure that that start date is always a Monday.
Run this little bit of code here you'll see that the start date is now whatever 4/05/06 is on that code.
Now let's go take a look and compare the PivotTable to how it looked at the beginning.
Initially we had daily dates going down here and now we have weekly dates going down the left-hand side.
So if you work in payroll or a manufacturing plant or anything where you need to roll things up to weekly dates, a great little trick there to choose only days.
Only days from the Grouping button, grouping dialog and then roll it up to seven-day period.
Well hey, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
