Today, in Episode #1304, Bill shows us how to use a Pivot Table in combination with Excel VBA [Visual Basic for Applications] to 'Roll-Up' dates to weekly increments.
From Chapter 13..."VBA And Macros: Microsoft Excel 2010", by Bill Jelen.
From Chapter 13..."VBA And Macros: Microsoft Excel 2010", by Bill Jelen.
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 am Bill Jelen. Working through the VBA book.
Chapter 13 is all about Pivot Tables, which are my favorite feature in Excel.
I love to do Pivot Tables in VBA. The example that I chose for the podcast is when 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 Pivot Table draw before we can go on. So normally, we make all the changes to the Pivot Table and then at the end refresh the Pivot Table but when you're grouping daily dates, you need to let the Pivot Table draw. So that we can select either the heading or the first date field either one and then 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, years, helps if you can remember the sequence of that because you're gonna have to be able to pass a series of false's and true's based on what you chose and in the macro what I'm going to do is I'm going to say "only days" that would 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.
Here we are we go...
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 true's in order and so you can see here that I'm using only the true's. 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 on Mondays. So, we'll run this little bit of code here.
You'll see that the start date is now whatever four or five or six is and run that code. Now, let's go take a look and compare the Pivot Table to how 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 group button, grouping dialog and then roll it up to seven-day period.
Well hey, I wanna 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 am Bill Jelen. Working through the VBA book.
Chapter 13 is all about Pivot Tables, which are my favorite feature in Excel.
I love to do Pivot Tables in VBA. The example that I chose for the podcast is when 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 Pivot Table draw before we can go on. So normally, we make all the changes to the Pivot Table and then at the end refresh the Pivot Table but when you're grouping daily dates, you need to let the Pivot Table draw. So that we can select either the heading or the first date field either one and then 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, years, helps if you can remember the sequence of that because you're gonna have to be able to pass a series of false's and true's based on what you chose and in the macro what I'm going to do is I'm going to say "only days" that would 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.
Here we are we go...
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 true's in order and so you can see here that I'm using only the true's. 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 on Mondays. So, we'll run this little bit of code here.
You'll see that the start date is now whatever four or five or six is and run that code. Now, let's go take a look and compare the Pivot Table to how 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 group button, grouping dialog and then roll it up to seven-day period.
Well hey, I wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
