Excel - Use VBA to Add Subtotals to the Footer of Each Page - Episode 1669

Microsoft Excel Tutorial: Use VBA to Add Subtotals to the Footer of Each Page. Welcome to another episode of the MrExcel Podcast. In today's episode, we will be discussing how to use VBA to add subtotals in the footer of an Excel spreadsheet. This is a follow-up to our previous episode, where we discussed using formulas to achieve the same result. If you would like to download the macro used in today's episode, please visit our website at www.mrexcel.com/podcast1669.html (all lowercase). In this episode, we will be addressing a question sent in by Amram, who wanted to see the total of certain amounts at the bottom of each printed page in the footer. While we did cover a formula-based approach in our previous episode, today we will be focusing on using VBA to achieve this task. The first step is to ensure that your file is saved as a macro-enabled workbook. If it is saved as an .xlsx file, you will need to save it as an .xlsm file to allow for macros. Next, we will be using the macro recorder to record two macros - one to change the footer and another to print just one page at a time. These macros will serve as a starting point for our final macro, which will use a loop to change the footer and print each page separately. We will also be using the Excel function "RoundUp" to ensure that our page count is always rounded up to the nearest whole number. Once we have our final macro, we will need to assign it to a shortcut key for easy access. This can be done by customizing the quick access toolbar and adding the macro to it. We will also be discussing our book, "VBA Macros" co-authored by Tracy Syrstad, which is available in four different editions and has been translated into multiple languages. Additionally, we have a live lesson series on Excel VBA macros available for those who prefer a more in-depth learning experience. Thank you for tuning in to today's episode of the MrExcel Podcast. We hope you found this tutorial on using VBA to add subtotals in the footer helpful. Don't forget to visit our website for the macro used in this episode and check out our book and live lesson series for more in-depth learning. We appreciate your support and look forward to bringing you more helpful Excel tips and tricks in future episodes. See you next time! Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/ You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-... Table of Contents: (00:00) Subtotal in Footer using VBA (00:40) Macro Approach Steps (00:50) Saving File as Macro-Enabled Workbook (01:00) Recording Macros (01:15) Use of VBA with Loop to Print One Page at a Time (01:32) Switching to VBA (02:20) Explanation of Main Macro (03:43) Explanation of Macro Code (04:10) Loop for Each Page (05:00) Changing Footer for Each Page (05:47) Running Macro Line by Line (06:59) Issue with Footer on Each Page (07:32) Clearing Footer for Non-Macro Users (08:00) Assigning Macro to Shortcut Key (08:31) Conclusion and Book/Video Recommendations (08:41) Clicking Like really helps the algorithm #excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial This video answers these common search terms: Download macro from MrExcel podcast episode 1669 Excel VBA How to change the footer in Excel How to print just one page at a time in Excel Macro-enabled workbook Page setup in Excel for printing Subtotal in footer with VBA Total amounts in footer for each printed page Using VBA to customize footer in Excel VBA loop for printing pages with changing footers Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads... **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 - http://learnmrexcel.wordpress.com/201... - 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