Pivottable - cycle through each PivotItem

hI kaper

thanks for a quick response.
Regardng the month selection, you were correct. I needed to update the code to ensure that everything else is not visible.

The pivotcache suggestion didn't work.
I suspect the problem is that I'm not actually selecting the pivotitem. I need to find a way to select the pivotitem, then do the copy-paste.

So probably it is worth considering part of: http://www.excelforum.com/forum-rule...rum-rules.html (near bottom):

Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

Seeing as I was only going to use this code once off, I went for a slightly different approach.

Essentially, the pivotitem needed to be selected using the .visible property.
In the original code in post 1 above, there isn't anything to change the property for each pi.

Here is the code that I used -

The references to "n" can be substituted with "For each pi in pivotfields....."
The learning point here is that the pi needs to be set to visible and other to false.

Thanks for your post though as it guided me in the right direction.

Sub Monthly_Movement_History()

Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim ws As Worksheet
Dim MthSelection As String

Set ws = ActiveSheet
Set pt = ActiveSheet.PivotTables(1)

For n = 1 To 48
MthSelection = WORKSHEETS("Pi.list").Range("A" & n).Value 'the list of all months (pivotitems) are listed on a separate sheet Pi.list

'first clear filters on Closed Month field
pt.PivotFields("Closed Month").ClearAllFilters

For Each pi In pt.PivotFields("Closed Month").PivotItems
If pi.name = MthSelection Then
pi.Visible = True
Else: pi.Visible = False
End If
Next pi

pt.TableRange1.Copy
WORKSHEETS.Add after:=ws
ActiveSheet.name = Right(MthSelection, 4) & Left(MthSelection, 3)
Range("A1").Select

With Selection
.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End With
ws.Select
Next n

End Sub