Pivot Table copy to other sheets

[thekgbjr]

I am creating about 50 invoices for a home health agency all on one workbook so there are 50 sheets, on each page of the invoice I would like to create a pivot table for that individual sheet. Is there a way to copy that sheet and make 49 additional copies that will automatically know to pull pivot table info from it's specific invoice, ie sheet1 copy to sheet2 and the pivot table on sheet2 knows to compile the data from sheet2 and not sheet1....is that making any sense whatsoever?

 

[rallcorn]

What version of Excel are you using?

You have an invoice number & want to generate each invoice on a separate worksheet?

 

[thekgbjr]

i'm on a Mac using 2008.
I have one workbook that contains 50 sheets, each sheet is an invoice for an individual member. I want to have a summary page that gets data from all 50 sheets onto one page using a Pivot Table. I would also like to have an individual pivot table on each sheet so that you can do a quick glance and see what services were used and how much we charged, it would be quicker than looking through a 100 line invoice trying to count how many injections, how many dressings, how many pillboxes etc you did for that member that month. I was wondering if there was a way to make 1 invoice and them make 49 copies but have the individual pivot tables know to not pull date from the original copy but from their own page.

 

[rallcorn]

If you have one sheet that contains ALL the data for ALL the invoices (i.e. one source document), I believe you could accomplish everything you've outlined and more.

Something similar to this as the SOURCE DATA for your PT:
<TABLE style="WIDTH: 242pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=322 border=0><COLGROUP><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2413" width=66><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 50pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=66 height=17>DATE</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>MEMBER</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>SERVICE</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>QTY</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>BILL AMT</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1/7/2011</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">12345</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Injection</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">1</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">15.00 </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl68 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1/10/2011</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">678910</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Dressing</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">2</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">45.00 </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>1/15/2011</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">12345</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Injection</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">1</TD><TD class=xl73 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">15.00 </TD></TR></TBODY></TABLE>


From Data Source sheet, create a PivotTalbe (PT) with "Member" in the Report Filter & create a separate invoice/PT for each member. (In PT Toolbar, Option Tab, PT, PT Options, Show Report Filter Pages - choose Member, OK, and a separate worksheet will be automatically be created for each member).

Even without creating these individual worksheets, from the PT you created you could do the kind of analysis on a particular member/service, etc.

 

[thekgbjr]

I think I follow that and it sounds like a perfect solution. So I would essentially have 1 invoice and would make a click down box for both the member section and service section. This invoice would be a master invoice, I could then create a pivot table using that data and by using the filter, I could filter the data I wanted by member, click print and there is their invoice?

 

[rallcorn]

Not exactly.

Source Data Sheet = all info for all members
PivotTable: Uses Source Data Sheet to create PT, put Member in Report Filter. Can do all sorts of analysis on one member & one service or All members one service, etc.

You wouldn't want to have to select each of the 50 members separately & print. To create the separate invoices AUTOMATICALLY: In PT Toolbar, Option Tab, PT, PT Options, Show Report Filter Pages - choose Member, OK, and a separate worksheet will be automatically be created for each member. This will create a separate worksheet for each member & you can print the entire workbook, do a macro to e-mail to each member's invoice to them, etc. The possibilities are unlimited!

 

[thekgbjr]

Can't wait to give this a shot, I have been watching a lot of the youtube videos on the subject of PT and it does seem like there are a world of possibilities!

THANK YOU

 

[thekgbjr]

90% there, got it working pretty well but am not able to get it to automatically print a page for each member.

 

[thekgbjr]

100% figured it out!

 

[rallcorn]

Great! Welcome to the world of PivotTable geeks!!!