Problem with pivot index
Hi,
I have the following code - it fails on the row where I put the stars. Could you help me please? The intention is for it to copy and paste a pivot table (with other cells) and change the page field of each pivot as it is pasted. Private Sub CommandButton1_Click() Dim CountryList As Range Dim CountryNum As Integer Dim CopyRange As Range Application.ScreenUpdating = False ActiveSheet.PivotTables("PvtCountry1").PivotFields ("Investigator Country"). _ CurrentPage = "(blank)" For Each CountryList In Sheets("Country Analysis").Range("Country_List").Cells CountryNum = ActiveSheet.PivotTables.Count If CountryNum = 1 Then Set CopyRange = Range("A11:G" & Cells(Rows.Count, "B").End(xlUp).Row + 1) Else CopyRange.Copy Range("A" & Cells(Rows.Count, "B").End(xlUp).Row + 2).Paste End If If CountryList < "" Then ActiveSheet.PivotTables(CountryNum).PivotFields("I nvestigator Country"). _ CurrentPage = CountryList '********************* End If Next CountryList Range("A2").Select Application.ScreenUpdating = True FrmOptimize.Hide End Sub Thanks, Basil |
Problem with pivot index
Countrylist is defined as a range object
despite value being the default property try CurrentPage = CountryList.Value might work in Xl2003, but not sure whether it'll work in 2007. You have to make sure the value of Countrylist is actually present in the pivottable field "Investigator Country", otherwise this can be another reason for an error. |
Problem with pivot index
Found a solution.
For some odd reason, when it copys and pastes a pivot table to the rows beneath, the new pivot table that has been created gains the index 1. I though pivottables(1) would be the one at the top, and as you add them beneath it would go to pivottables(2) etc. Anyone know how that works? "Basil" wrote: Hi, I have the following code - it fails on the row where I put the stars. Could you help me please? The intention is for it to copy and paste a pivot table (with other cells) and change the page field of each pivot as it is pasted. Private Sub CommandButton1_Click() Dim CountryList As Range Dim CountryNum As Integer Dim CopyRange As Range Application.ScreenUpdating = False ActiveSheet.PivotTables("PvtCountry1").PivotFields ("Investigator Country"). _ CurrentPage = "(blank)" For Each CountryList In Sheets("Country Analysis").Range("Country_List").Cells CountryNum = ActiveSheet.PivotTables.Count If CountryNum = 1 Then Set CopyRange = Range("A11:G" & Cells(Rows.Count, "B").End(xlUp).Row + 1) Else CopyRange.Copy Range("A" & Cells(Rows.Count, "B").End(xlUp).Row + 2).Paste End If If CountryList < "" Then ActiveSheet.PivotTables(CountryNum).PivotFields("I nvestigator Country"). _ CurrentPage = CountryList '********************* End If Next CountryList Range("A2").Select Application.ScreenUpdating = True FrmOptimize.Hide End Sub Thanks, Basil |
All times are GMT +1. The time now is 04:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com