Automate Pivot Table Report

[Excel_Newbie]

Hello everyone,

I hope that somebody can help me on automating this excel report.

This is an Excel Revenue report. Everytime I have to submit this excel report, I have to manually select the month and each region from this pivot table to get the data for each of the regions; and then do all the calculations before copying the data to a different excel report.

I am trying to automat this because there are over 100 regions, and it takes me alot of time to manually do this before sending away the report.

Is there anyway, excel can select the max date from the month list and each of the regions each time it refreshes and then copy the data (just the values only not the formulas) to another sheet of excel or another workbook?

If you don't know how to do what I am asking above, do you have any ideas on how to copy just the calculated values and past them to another sheet or workbook?

I think I can just take away the Month and Region from my pivot table and then just retrieve data from a table that only contains a specific month and region each time (this is going to be a loop). But the problem is that I don't know how to copy the calculated values to another worksheet or workbook automatically. I don't want to copy and past special manually for this.

Thank you so much for your help.

[bpeltzer]

It sounds like the pain is in having to create the PivotTable for each
region, just because there are so many of them. Is the Region, by chance, a
Page Field (as opposed to a row or column header)? From the sounds of it,
I'm guessing that may be the case. If so, first create ONE pivot table set
up as you want it, with just the appropriate date range, etc. Then click the
Pivot Table drop-down on the left side of the Pivot Table toolbar (View >
Toolbars and select Pivot Table if it isn't visible already). From the
drop-down select ShowPages... and double-click Region. You'll see copies of
the PivotTable you've created, one for each Region on a worksheet named for
that Region.
(As for getting all the data from the pivots to your final report, I might
set up a bunch of lookups that would automate the retrieval from pivots to
whatever form you need. Then I'd make a copy of that file and paste values
before sending it out).
HTH. --Bruce

"Excel_Newbie" wrote:

>
> Hello everyone,
>
> I hope that somebody can help me on automating this excel report.
>
> This is an Excel Revenue report. Everytime I have to submit this excel
> report, I have to manually select the month and each region from this
> pivot table to get the data for each of the regions; and then do all
> the calculations before copying the data to a different excel report.
>
> I am trying to automat this because there are over 100 regions, and it
> takes me alot of time to manually do this before sending away the
> report.
>
> Is there anyway, excel can select the max date from the month list and
> each of the regions each time it refreshes and then copy the data (just
> the values only not the formulas) to another sheet of excel or another
> workbook?
>
> If you don't know how to do what I am asking above, do you have any
> ideas on how to copy just the calculated values and past them to
> another sheet or workbook?
>
> I think I can just take away the Month and Region from my pivot table
> and then just retrieve data from a table that only contains a specific
> month and region each time (this is going to be a loop). But the
> problem is that I don't know how to copy the calculated values to
> another worksheet or workbook automatically. I don't want to copy and
> past special manually for this.
>
> Thank you so much for your help.
>
>
> --
> Excel_Newbie
> ------------------------------------------------------------------------
> Excel_Newbie's Profile: http://www.excelforum.com/member.php...o&userid=28861
> View this thread: http://www.excelforum.com/showthread...hreadid=486116
>
>