MrExcel's Learn Excel #740 - Insert Worksheets II

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Feb 10, 2009.
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!
maxresdefault.jpg


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.
 

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top