B
Basil
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
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