Jerry from Georgia sends in a great alternate solution to podcast 730, inserting one worksheet per customer. Gerry uses the Show Pages feature of pivot tables. Episode 740 shows you how.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
Let's go back to Episode 730.
JB had written in he said, " hey, I have a list of customers here.
I need to create one worksheet for every customer and in that episode, I used some VBA to do that " Well, I got a voicemail from Jerry.
Jerry and Georgia.
Jerry says, "Hey, there's a much better way to do it without using any code".
And he's absolutely right.
It uses one of my favorite tricks of all time.
If we go to Data>Pivot Table...
We're going to create a really simple pivot table here with Customer.
That's the field that we want a bunch of worksheets for.
Up in the page area.
And then just one field data in the data items.
And once we have this pivot table with customer up there, we're going to go to the PivotTable drop-down and choose Show Pages.
Show all pages of: Customer and in an instant, we get a whole bunch of new Worksheets.
You'll see them here at A136, A149, A174, A179.
Basically, one worksheet for every customer in the data set.
I'm going to choose all of those new worksheets.
All..
Click on the first one and then Shift.
Click on the last one.
This now puts me in group mode.
Once I've selected all the sheets then I just select the rows where the pivot table is.
And we can use Edit>Clear All and we now have a whole series of empty sheets.
Properly named! One for every customer in our original data set.
Great tip from Jerry.
I want to thank him for sending it in and want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I am Bill Jelen.
Let's go back to Episode 730.
JB had written in he said, " hey, I have a list of customers here.
I need to create one worksheet for every customer and in that episode, I used some VBA to do that " Well, I got a voicemail from Jerry.
Jerry and Georgia.
Jerry says, "Hey, there's a much better way to do it without using any code".
And he's absolutely right.
It uses one of my favorite tricks of all time.
If we go to Data>Pivot Table...
We're going to create a really simple pivot table here with Customer.
That's the field that we want a bunch of worksheets for.
Up in the page area.
And then just one field data in the data items.
And once we have this pivot table with customer up there, we're going to go to the PivotTable drop-down and choose Show Pages.
Show all pages of: Customer and in an instant, we get a whole bunch of new Worksheets.
You'll see them here at A136, A149, A174, A179.
Basically, one worksheet for every customer in the data set.
I'm going to choose all of those new worksheets.
All..
Click on the first one and then Shift.
Click on the last one.
This now puts me in group mode.
Once I've selected all the sheets then I just select the rows where the pivot table is.
And we can use Edit>Clear All and we now have a whole series of empty sheets.
Properly named! One for every customer in our original data set.
Great tip from Jerry.
I want to thank him for sending it in and want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
