Automate Pivot Table Report
 

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

Automate Pivot Table Report
 

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