Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating multiple pivot table report at run time
Hi,
I'm trying to automate the creation of a report through VBA and desperately need some help. I have a named range 'Countries' on sheet 1. On sheet 2 there is loads and loads of data related to each country and various cities within each country. The number of cities varies per country and the list of countries will change each time this report is run (source data will change). On sheet 3: A - rows 1 to 10 are static B - rows 11 to 26 are formulaic and lookup bits of information for country 1 C - row 27 is the top of a PivotTable1 extracting data for cities in country 1 (varies in record count depending on what country 1 is - the data set changes each time this is done) To create the report: 1. PivotTable1 page field is set to country 1 (the top row of the 'Countries' named range) 2. Rows 11 to the bottom of PivotTable1 (which varies) are copied to the clipboard 3. 2 rows below the bottom of PivotTable1 (in column A), the cell is selected and clipboard pasted 4. In this new pivot table that has been pasted, the page field is set to the second country in the 'Countries' list 5. The above steps are repeated until the full list of countries has had its formulas and pivot table pasted. I'm really struggling with the code here to reference the right rows to copy and where to paste . Can somebody please give me some tips or example helper code please? Many thanks, Basil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to drag multiple items to pivot table report at once | Excel Discussion (Misc queries) | |||
creating a report from a table, pivot table not suitable | Excel Worksheet Functions | |||
FRom Pivot table select multiple items under report filter | Excel Programming | |||
Creating Multiple Charts from Same Pivot Table using macro | Excel Programming | |||
How do I use multiple worksheets (List) in a Pivot Table Report? | Excel Worksheet Functions |