***We had a slight mix up of Podcast Versions: This is the corrected version of Podcast #1669, showing a slide as to where you may download the Sample Files for both Podcast #1668 and #1669.***
First, save your file as a Macro-Enabled Workbook - " .xlsm " - and now you need to following along with Episode #1669 as Bill shows us the VBA Macro Solution to showing Subtotals in the Footer of our Printed Page. [If you would like a Formula-based solution to this question, see Podcast #1668 - Learn Excel 2013 – “Subtotal in Footer” Podcast #1668 Part 1 - to achieve the result without the use of VBA [Visual Basic for applications].
The Sample Files for these Podcasts #1668 and #1669 may be downloaded Here from the MrExcel Website! http://www.mrexcel.com/podcast1669.html
...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!
First, save your file as a Macro-Enabled Workbook - " .xlsm " - and now you need to following along with Episode #1669 as Bill shows us the VBA Macro Solution to showing Subtotals in the Footer of our Printed Page. [If you would like a Formula-based solution to this question, see Podcast #1668 - Learn Excel 2013 – “Subtotal in Footer” Podcast #1668 Part 1 - to achieve the result without the use of VBA [Visual Basic for applications].
The Sample Files for these Podcasts #1668 and #1669 may be downloaded Here from the MrExcel Website! http://www.mrexcel.com/podcast1669.html
...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 1669: Subtotal in footer with VBA.
To download the macro from today's podcast go to http://www.mrexcel.com/podcast1669.html, that's all lowercase.
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday Amram sent in, actually over the weekend Amram sent in a question where he wants at the bottom of every page in the footer, he wants to see the total of these amounts so far and he wants a different total at the bottom of each printed page.
Yesterday, I didn't actually put in the footer but I used a formula out here so if you want to go with just the formula approach go back to episode 1668 but if you want to try the macro approach here's some steps we have to do.
First, if your file is not saved as xlsm but saved as xlxs we need to save as a macro-enabled workbook.
So go to File, Save As and then down here in the files of type switch to Excel Macro-Enabled Workbook otherwise you're not going to be allowed to have macros in your file.
I then recorded two macros, “How to change the footer” and “How to print just page 2.” I knew that both of those were possible in Excel.
l didn't know the code off the top of my head so I recorded those macros and then the real subject of today's podcast is “How to use VBA with a loop to print just one page at a time” changing the footer in each case.
All right, so I'm going to switch over to VBA, Alt+F11 and let's just take a quick look down here at the two macros that I recorded.
“How to print just page 2,” and I could have chosen any page but it recorded this code “ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2” Full Formula ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2, Copies:=1, Collate _ :=True, IgnorePrintAreas:=False So when I incorporate this into my macro I'm going have to change that code.
All right, and then the macro recorder, you know I love the macro recorder and I hate the macro recorder, so all I did was to type “Total This Page $” in the left footer and “Total Through This Page $” in the right footer and they recorded all of this stuff that I don't really need, right, because I touched the page setup dialog box that recorded everything in there.
We're going shorten this significantly in our code.
All right, okay so let's go back up to our macro.
This is the macro that I want to talk about today.
All right, so we start out with some things that you can fill in.
Where's your first data row?
It's in row 6.
How many rows per page?
We figured this out yesterday by looking at print preview and of course you want to make sure that all of your page setup is already done and then column to total, it’s column E you'll see is the column to total and we want to make sure, so just count.
So one, two, three, four, five; it's the fifth column so you put the column number there.
All right, how many rows do we have today?
Well the head row is simple it's the first data row minus 1.
As for Final row, this is the trick I use all the time I go to Rows.Count, that's the last row in column A. This is column 1 so press the end key and the up arrow key or Ctrl+Up arrow key and see what row we are at.
It will tell us how many rows we have.
PageCount is = (FinalRow – HeadRow) / by RowsPerPage and that's going to be something like 8.3, all right, it's hardly ever going to be perfect so I want to round that up to get the page count.
So the PageCount is = Application.WorksheetFunction.RoundUp.
All right, so we have round in VBA but we don't have roundUp but I can use the Excel functions by using Application.Worksheet function.
The page count to zero decimal places.
Now that way if it happens to be exactly 8 pages, 8.000 I'm still going get 8 otherwise I'm going get one more.
Now here's the loop [ see formula below ].
Sub PrintWithTotals() ‘ Fill in this information FirstDataRow = 6 RowsPerPage = 40 ColToTotal = 5 ‘ Column E is the 5th column… ‘ Find how many rows today HeadRow = FirstDataRow – 1 FinalRow = Cells(Rows.Count, 1).
End(x1Up).Row PageCount = (FinalRow – HeadRow) / RowsPErPage PageCount = Application.WorksheetFunction.RoundUp(PageCount, 0) For i =1 To PageCount ThisPageFirstRow = (i - 1) * RowsPerPage+ HeadRow + 1 ThisPageLastRow = ThisPageFirstRow + RowsPerPage – 1 TotalThisPage = Application.WorksheetFunction.
_ Sum(Cells(ThisPageFirstRow, ColToTotal) .Resize(RowsPerPage * i, 1)) TotalAllPages = Application.WorksheetFunction.
_ Sum(Cells(FirstDataRow, ColToTotal) .Resize(RowsPerPage * i, 1)) ‘ Change the Footer for this page Application.PrintCommunication = False With ActiveSheet.PageSetup .Leftfooter = “Total This Page $” & Format(TotalThisPage, “#,##0.00”) .RightFooter = “Total Through This Page $” & Format(TotalALLPages, “#,##0.00”) End With Application.PrintCommunication = True ‘ Print this page ActiveWindow.SelectedSheets.PrintOut From:=I, To:=I, Copies:=1, Collate _ :=True, IgnorePrintAreas:=False Next i ‘ Clear the footer in case someone prints without the Macro Application.PrintCommunication = False With ActiveSheet.PageSetup .Leftfooter = “Use PrintWithTotals Macro”) .RightFooter = “” End With Application.PrintCommunication = True End Sub This is for everything between For I and the next I is going happen once per page and each time we're going have a variable called I that's going to indicate the page number.
All right so we figure out where the first row of data is today.
That's the page number minus one times RowsPerPage plus the HeadRow plus one ThisPageLastRow is equal to ThisPageFirstRow plus RowsPerPage minus one, and then calculate on-the-fly the total for just ThisPage and I use Cells to start at ThisPageFirstRow which column?
Column 5 and then resize it for RowsPerPage * i.
I'm sorry resize it for RowsPerPage, one column and that'll create the total for this page to get the total of all pages I start from the first data row and RowsPerPage * i.
So if I'm on the seventh page 7 * 4 is 280 rows.
Those two variables will hold the total of this page in total of all page.
Now we're going change the footer all right so I take my copied or my recorded code down here which has way too much in it I get rid of all the stuff I don't need and so with active sheet page setup' stays, the left footer stays and the center footer goes, the right footer stays.
Everything else all the way down to End With goes away.
So this becomes the bit of recorded code that I'm going use over and over and over.
Copy that Ctrl+ C, come up and change the footer for this page right here.
Ctrl+ V.
But, we need to put in the actual amount so here's the recorded code I've put an ampersand and then formatted the variable TotalThisPage with this format.
You can choose whatever format you want.
Total through this page format TotalAllPages.
All right, so I'm actually changing the left footer and the right footer on-the-fly then, it's funny I'm not just changing the footer for that page I'm changing the footer for all pages so I have to be very careful to only print this particular page.
We then go back up.
All right now I'm going run this one line at a time using F8 and I'm going to let it go through the first page because I want to show you what's happening on the second page.
So here we are this is the second time through the loop for i equals 1 to the page count so i, we are on page number 2.
ThisPageFirstRow, right now I have to press F8 to run it.
ThisPageFirstRow is going to be 46.
Press F8 one more time ThisPageLastRow is 85.
TotalThisPage 95 100.
TotalAllPages, it's really all pages so far 191 100.
These are the exact same numbers that we were getting yesterday.
All right then here we actually go in and change the footer.
All right now at this point let's switch back to Excel and we'll take a look at our footer so go into Print and we'll zoom in right here.
Total this page is 95 100.
Total Through this page is 191 100 and has the nice commas, the two decimal places but here's the thing this is not the total for page 1.
It's incorrect as I click through the pages you see that the footer is the same on every single page and that's not correct.
So we have to be really careful here that we're only printing that particular page.
So we're printing page 2, yeah good and we will let the thing run.
At the very bottom then, we need to clear out the footer because maybe, someday Amram won't show up to work, he's sick or had to take his kid to school, who knows and someone else is going to run this code and they're not going to know about the macro and they're just going to print using Ctrl+P and so I'm going change that footer to use Print with totals macro and so on.
So here let's run down to this point Ctrl+F8 and let the code roam and there we go.
All right so now if we go back and take a look at our page setup, so now the footers have been erased and say “Use PrintWith totals Macro” All right so there we go we have to assign this to a shortcut key.
Customize quick access toolbar, look for macros and there's our PrintWithTotals.
We want it not for all documents but just for this document so we'll add it.
Let's see if we can modify, I don't know if there's anything in here that looks like a printer, maybe yeah and change the description, click OK click OK.
All right so now we have an icon up here that is PrintWithTotals that icon will only be here when this workbook is open.
All right, very long-winded very tough to do in a quick little podcast here but a macro will allow a different footer on each page.
It's absolutely amazing what you can do with VBA.
If you're interested in learning VBA, Tracy Syrstad and I have written a book called VBA Macros.
It's out in four different editions Excel 2013, 2010, 2007, 2003.It’s also been translated to several different languages Spanish, Portuguese, Ukrainian on and on and on Also live lessons Excel VBA macros with MrExcel.
If you're watching the podcast it's videos just like this one although a lot slower 7+ hours that you can watch you know 5 or 10 minutes a day and learn VBA.
All right, well, hey 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 1669: Subtotal in footer with VBA.
To download the macro from today's podcast go to http://www.mrexcel.com/podcast1669.html, that's all lowercase.
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday Amram sent in, actually over the weekend Amram sent in a question where he wants at the bottom of every page in the footer, he wants to see the total of these amounts so far and he wants a different total at the bottom of each printed page.
Yesterday, I didn't actually put in the footer but I used a formula out here so if you want to go with just the formula approach go back to episode 1668 but if you want to try the macro approach here's some steps we have to do.
First, if your file is not saved as xlsm but saved as xlxs we need to save as a macro-enabled workbook.
So go to File, Save As and then down here in the files of type switch to Excel Macro-Enabled Workbook otherwise you're not going to be allowed to have macros in your file.
I then recorded two macros, “How to change the footer” and “How to print just page 2.” I knew that both of those were possible in Excel.
l didn't know the code off the top of my head so I recorded those macros and then the real subject of today's podcast is “How to use VBA with a loop to print just one page at a time” changing the footer in each case.
All right, so I'm going to switch over to VBA, Alt+F11 and let's just take a quick look down here at the two macros that I recorded.
“How to print just page 2,” and I could have chosen any page but it recorded this code “ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2” Full Formula ActiveWindow.SelectedSheets.PrintOut From:=2, to:=2, Copies:=1, Collate _ :=True, IgnorePrintAreas:=False So when I incorporate this into my macro I'm going have to change that code.
All right, and then the macro recorder, you know I love the macro recorder and I hate the macro recorder, so all I did was to type “Total This Page $” in the left footer and “Total Through This Page $” in the right footer and they recorded all of this stuff that I don't really need, right, because I touched the page setup dialog box that recorded everything in there.
We're going shorten this significantly in our code.
All right, okay so let's go back up to our macro.
This is the macro that I want to talk about today.
All right, so we start out with some things that you can fill in.
Where's your first data row?
It's in row 6.
How many rows per page?
We figured this out yesterday by looking at print preview and of course you want to make sure that all of your page setup is already done and then column to total, it’s column E you'll see is the column to total and we want to make sure, so just count.
So one, two, three, four, five; it's the fifth column so you put the column number there.
All right, how many rows do we have today?
Well the head row is simple it's the first data row minus 1.
As for Final row, this is the trick I use all the time I go to Rows.Count, that's the last row in column A. This is column 1 so press the end key and the up arrow key or Ctrl+Up arrow key and see what row we are at.
It will tell us how many rows we have.
PageCount is = (FinalRow – HeadRow) / by RowsPerPage and that's going to be something like 8.3, all right, it's hardly ever going to be perfect so I want to round that up to get the page count.
So the PageCount is = Application.WorksheetFunction.RoundUp.
All right, so we have round in VBA but we don't have roundUp but I can use the Excel functions by using Application.Worksheet function.
The page count to zero decimal places.
Now that way if it happens to be exactly 8 pages, 8.000 I'm still going get 8 otherwise I'm going get one more.
Now here's the loop [ see formula below ].
Sub PrintWithTotals() ‘ Fill in this information FirstDataRow = 6 RowsPerPage = 40 ColToTotal = 5 ‘ Column E is the 5th column… ‘ Find how many rows today HeadRow = FirstDataRow – 1 FinalRow = Cells(Rows.Count, 1).
End(x1Up).Row PageCount = (FinalRow – HeadRow) / RowsPErPage PageCount = Application.WorksheetFunction.RoundUp(PageCount, 0) For i =1 To PageCount ThisPageFirstRow = (i - 1) * RowsPerPage+ HeadRow + 1 ThisPageLastRow = ThisPageFirstRow + RowsPerPage – 1 TotalThisPage = Application.WorksheetFunction.
_ Sum(Cells(ThisPageFirstRow, ColToTotal) .Resize(RowsPerPage * i, 1)) TotalAllPages = Application.WorksheetFunction.
_ Sum(Cells(FirstDataRow, ColToTotal) .Resize(RowsPerPage * i, 1)) ‘ Change the Footer for this page Application.PrintCommunication = False With ActiveSheet.PageSetup .Leftfooter = “Total This Page $” & Format(TotalThisPage, “#,##0.00”) .RightFooter = “Total Through This Page $” & Format(TotalALLPages, “#,##0.00”) End With Application.PrintCommunication = True ‘ Print this page ActiveWindow.SelectedSheets.PrintOut From:=I, To:=I, Copies:=1, Collate _ :=True, IgnorePrintAreas:=False Next i ‘ Clear the footer in case someone prints without the Macro Application.PrintCommunication = False With ActiveSheet.PageSetup .Leftfooter = “Use PrintWithTotals Macro”) .RightFooter = “” End With Application.PrintCommunication = True End Sub This is for everything between For I and the next I is going happen once per page and each time we're going have a variable called I that's going to indicate the page number.
All right so we figure out where the first row of data is today.
That's the page number minus one times RowsPerPage plus the HeadRow plus one ThisPageLastRow is equal to ThisPageFirstRow plus RowsPerPage minus one, and then calculate on-the-fly the total for just ThisPage and I use Cells to start at ThisPageFirstRow which column?
Column 5 and then resize it for RowsPerPage * i.
I'm sorry resize it for RowsPerPage, one column and that'll create the total for this page to get the total of all pages I start from the first data row and RowsPerPage * i.
So if I'm on the seventh page 7 * 4 is 280 rows.
Those two variables will hold the total of this page in total of all page.
Now we're going change the footer all right so I take my copied or my recorded code down here which has way too much in it I get rid of all the stuff I don't need and so with active sheet page setup' stays, the left footer stays and the center footer goes, the right footer stays.
Everything else all the way down to End With goes away.
So this becomes the bit of recorded code that I'm going use over and over and over.
Copy that Ctrl+ C, come up and change the footer for this page right here.
Ctrl+ V.
But, we need to put in the actual amount so here's the recorded code I've put an ampersand and then formatted the variable TotalThisPage with this format.
You can choose whatever format you want.
Total through this page format TotalAllPages.
All right, so I'm actually changing the left footer and the right footer on-the-fly then, it's funny I'm not just changing the footer for that page I'm changing the footer for all pages so I have to be very careful to only print this particular page.
We then go back up.
All right now I'm going run this one line at a time using F8 and I'm going to let it go through the first page because I want to show you what's happening on the second page.
So here we are this is the second time through the loop for i equals 1 to the page count so i, we are on page number 2.
ThisPageFirstRow, right now I have to press F8 to run it.
ThisPageFirstRow is going to be 46.
Press F8 one more time ThisPageLastRow is 85.
TotalThisPage 95 100.
TotalAllPages, it's really all pages so far 191 100.
These are the exact same numbers that we were getting yesterday.
All right then here we actually go in and change the footer.
All right now at this point let's switch back to Excel and we'll take a look at our footer so go into Print and we'll zoom in right here.
Total this page is 95 100.
Total Through this page is 191 100 and has the nice commas, the two decimal places but here's the thing this is not the total for page 1.
It's incorrect as I click through the pages you see that the footer is the same on every single page and that's not correct.
So we have to be really careful here that we're only printing that particular page.
So we're printing page 2, yeah good and we will let the thing run.
At the very bottom then, we need to clear out the footer because maybe, someday Amram won't show up to work, he's sick or had to take his kid to school, who knows and someone else is going to run this code and they're not going to know about the macro and they're just going to print using Ctrl+P and so I'm going change that footer to use Print with totals macro and so on.
So here let's run down to this point Ctrl+F8 and let the code roam and there we go.
All right so now if we go back and take a look at our page setup, so now the footers have been erased and say “Use PrintWith totals Macro” All right so there we go we have to assign this to a shortcut key.
Customize quick access toolbar, look for macros and there's our PrintWithTotals.
We want it not for all documents but just for this document so we'll add it.
Let's see if we can modify, I don't know if there's anything in here that looks like a printer, maybe yeah and change the description, click OK click OK.
All right so now we have an icon up here that is PrintWithTotals that icon will only be here when this workbook is open.
All right, very long-winded very tough to do in a quick little podcast here but a macro will allow a different footer on each page.
It's absolutely amazing what you can do with VBA.
If you're interested in learning VBA, Tracy Syrstad and I have written a book called VBA Macros.
It's out in four different editions Excel 2013, 2010, 2007, 2003.It’s also been translated to several different languages Spanish, Portuguese, Ukrainian on and on and on Also live lessons Excel VBA macros with MrExcel.
If you're watching the podcast it's videos just like this one although a lot slower 7+ hours that you can watch you know 5 or 10 minutes a day and learn VBA.
All right, well, hey I want to thank you for stopping by.
We’ll see you next time for another netcast from MrExcel.
