Jim wants to add a Page Break between each Department in a large Data file. Using Subtotals would be the fastest way, but he does not need a subtotal. So, instead of using Subtotals, in Episode 1629 Bill shows us a hybrid method. Using a Formula and 'Go To Special' to select the first record for each department, a short, 3-line Macro will add the Page Breaks right where Jim needs them.
...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 from MrExcel podcast, episode, 1629.
Page Break at each change in Department.
Hey! Welcome back to the MrExcel netcast.
Today's question sent in by Jim.
Jim gets payroll data and there's a different number of records every week.
This week, we have three thousand, some records and he needs a way to insert a page break at each change in department and I said hey, the fastest way to do this, just add a subtotal and say that you're going to allow a page break, after each change in department.
So, here I'll click subtotal just outside of review, check this box, page break between groups and now I don't want the subtotal there.
That's a problem signs, all right!
Then we're going to use, this method which I've used in the past.
I'm going to add a temp field here, start in the second record equal IF.
This department is not equal to the previous department, then I want a 1, otherwise I want some sort of text even quote quote will work.
Double click to copy that down and then I'm going to use [ ctrl G ], for go to, click special and say that I want all of the formulas that result in numbers.
So, I'm unchecking text, click [ ok ], that gets every new department line selected then I'm going to switch over to VBA, [ alt F11 ].
We're have this tiny little macro.
Now, you can put this in your personal macro workbook or frankly you can just type it.
Every time that you need to do this for each cell in selection.
Active window dot active sheet dot H of course, page breaks dot add.
The continuation character before cell, next cell just click [ run ].
Wow! Look how fast that was, come back, and you'll see that we now, have a page break at each new department at this point, we're done.
We can delete this temporary column and...
Hey! I want to thank your stopping by, we'll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel podcast, episode, 1629.
Page Break at each change in Department.
Hey! Welcome back to the MrExcel netcast.
Today's question sent in by Jim.
Jim gets payroll data and there's a different number of records every week.
This week, we have three thousand, some records and he needs a way to insert a page break at each change in department and I said hey, the fastest way to do this, just add a subtotal and say that you're going to allow a page break, after each change in department.
So, here I'll click subtotal just outside of review, check this box, page break between groups and now I don't want the subtotal there.
That's a problem signs, all right!
Then we're going to use, this method which I've used in the past.
I'm going to add a temp field here, start in the second record equal IF.
This department is not equal to the previous department, then I want a 1, otherwise I want some sort of text even quote quote will work.
Double click to copy that down and then I'm going to use [ ctrl G ], for go to, click special and say that I want all of the formulas that result in numbers.
So, I'm unchecking text, click [ ok ], that gets every new department line selected then I'm going to switch over to VBA, [ alt F11 ].
We're have this tiny little macro.
Now, you can put this in your personal macro workbook or frankly you can just type it.
Every time that you need to do this for each cell in selection.
Active window dot active sheet dot H of course, page breaks dot add.
The continuation character before cell, next cell just click [ run ].
Wow! Look how fast that was, come back, and you'll see that we now, have a page break at each new department at this point, we're done.
We can delete this temporary column and...
Hey! I want to thank your stopping by, we'll see you next time for another netcast from MrExcel.
