Today -in the concluding edition of the 'Print Form from Database' series- Bill introduces a Visual Basic for Applications Macro that handles the work necessary for Mike to print each record in a Data Set [that is pulled from an Excel Database]. Follow along with Bill, in Episode #1549, to learn how to implement the code and find out just how easy it is!
...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!
...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 for MrExcel Podcast, Episode 1549: Print Each Record with Macro.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
All this week, episode 1546 through 1548, we talked about the situation where Mike gets data pasted into the data worksheet each day, and for every record he needs to format that into a report and print the report.
And we got it down through yesterday, where it's, kind of, the process is like this-- you look at the data worksheet, you figure out how many rows we have today, so we have to go from 2 to 12 or, actually, from 1 to 11-- there's 11 records-- and then come here, he puts in the number 1, Ctrl+P to print, puts in the number 2, Ctrl+P to print, prints, and number 3...
Isn't that tedious?
Anytime you have something tedious, bet a macro could do it faster.
So, I saved this workbook as a macro-enabled workbook-- so File, Save As, xlsm, and then we're going to do Alt+F11, we do Insert, Module, and then I just knocked out this short little macro-- count how many rows are on the data worksheet, we start at the very last row, press the N key and the Up arrow key, that's xlUp, figure out the row we're on and subtract 1 from it-- but that's because we're using the offset function.
And then select the “Report” Worksheet, we're going to go from 1 to row count each time through, we're going to go back to that cell B1, where we put the record number, we set the value of B1 is equal to I, and then ActiveSheet.PrintOut Copies:=1.
So, what this should do, is no matter how many rows we have back on the data worksheet, it'll go from 1 down to the last row, print that out, and it should just be a single keystroke.
So we have Print All, click back here in Excel, to assign that to a shortcut key I use Alt+F8, we'll choose Print All, and then options.
Let's do Ctrl+Shift+P for print, click OK.
Ctrl+P is just a regular print; Ctrl+Shift+P will run the Print All macro.
I can click cancel now, at this point.
This doesn't have to start at 1.
It's going to work automatically.
We’ll do Ctrl+Shift+P and, just that fast, I now have 11 pages printing out.
We have-- it went through the 11 records.
If we would have less records tomorrow-- so I'll delete these, come back here Ctrl+Shift+P.
I just ran through records 1 through 5, getting our last record each day.
So, Macro makes this very easy-- come in, paste the data-- depending on whether you have headings or not, whether it goes to A1 or starts in a 2-- and then just come back and Ctrl+Shift+P. Life will be simple that way.
Hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
All this week, episode 1546 through 1548, we talked about the situation where Mike gets data pasted into the data worksheet each day, and for every record he needs to format that into a report and print the report.
And we got it down through yesterday, where it's, kind of, the process is like this-- you look at the data worksheet, you figure out how many rows we have today, so we have to go from 2 to 12 or, actually, from 1 to 11-- there's 11 records-- and then come here, he puts in the number 1, Ctrl+P to print, puts in the number 2, Ctrl+P to print, prints, and number 3...
Isn't that tedious?
Anytime you have something tedious, bet a macro could do it faster.
So, I saved this workbook as a macro-enabled workbook-- so File, Save As, xlsm, and then we're going to do Alt+F11, we do Insert, Module, and then I just knocked out this short little macro-- count how many rows are on the data worksheet, we start at the very last row, press the N key and the Up arrow key, that's xlUp, figure out the row we're on and subtract 1 from it-- but that's because we're using the offset function.
And then select the “Report” Worksheet, we're going to go from 1 to row count each time through, we're going to go back to that cell B1, where we put the record number, we set the value of B1 is equal to I, and then ActiveSheet.PrintOut Copies:=1.
So, what this should do, is no matter how many rows we have back on the data worksheet, it'll go from 1 down to the last row, print that out, and it should just be a single keystroke.
So we have Print All, click back here in Excel, to assign that to a shortcut key I use Alt+F8, we'll choose Print All, and then options.
Let's do Ctrl+Shift+P for print, click OK.
Ctrl+P is just a regular print; Ctrl+Shift+P will run the Print All macro.
I can click cancel now, at this point.
This doesn't have to start at 1.
It's going to work automatically.
We’ll do Ctrl+Shift+P and, just that fast, I now have 11 pages printing out.
We have-- it went through the 11 records.
If we would have less records tomorrow-- so I'll delete these, come back here Ctrl+Shift+P.
I just ran through records 1 through 5, getting our last record each day.
So, Macro makes this very easy-- come in, paste the data-- depending on whether you have headings or not, whether it goes to A1 or starts in a 2-- and then just come back and Ctrl+Shift+P. Life will be simple that way.
Hey, I want to thank you for stopping by, I'll see you next time for another netcast from MrExcel.
