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

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.

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