Hi,
Wondering if someone could help. I keep getting a runtime 1004 error when I run the following code, even though it has been ok up until now. Last time, I thought it maybe that the file was too big, so reduced the size of the file, but this time it doesnt seem to be working. Can anyone suggest something?
Sub GeneratePages()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iItem As Integer
Dim iNext As Integer
Dim str As String
Set pt = Sheet1.PivotTables(1)
Set pf = pt.PageFields("Owner")
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields("Owner").CurrentPage = pi.Name
For iItem = 1 To pf.PivotItems.Count
If pf.PivotItems(iItem).Visible = True Then
iNext = iItem + 1
Exit For
End If
Next iItem
If iNext > pf.PivotItems.Count Then
iNext = 1
End If
ActiveSheet.Copy Before:=Sheets("Control")
str = pf.PivotItems(iNext).Name
pf.CurrentPage = pf.PivotItems(str).Name
Sheet1.Activate
Next
Next pf
End Sub
Thanks
Wondering if someone could help. I keep getting a runtime 1004 error when I run the following code, even though it has been ok up until now. Last time, I thought it maybe that the file was too big, so reduced the size of the file, but this time it doesnt seem to be working. Can anyone suggest something?
Sub GeneratePages()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim iItem As Integer
Dim iNext As Integer
Dim str As String
Set pt = Sheet1.PivotTables(1)
Set pf = pt.PageFields("Owner")
For Each pf In pt.PageFields
For Each pi In pf.PivotItems
pt.PivotFields("Owner").CurrentPage = pi.Name
For iItem = 1 To pf.PivotItems.Count
If pf.PivotItems(iItem).Visible = True Then
iNext = iItem + 1
Exit For
End If
Next iItem
If iNext > pf.PivotItems.Count Then
iNext = 1
End If
ActiveSheet.Copy Before:=Sheets("Control")
str = pf.PivotItems(iNext).Name
pf.CurrentPage = pf.PivotItems(str).Name
Sheet1.Activate
Next
Next pf
End Sub
Thanks