Hi Guys,

Do you know how to print all the pivot items separately (instead of printing
the whole pivot table...)?
Intuitively I think about a macro that will show one pivot item after
another then print the page, but I'm failing to achieve this through VBA
coding, since already 3 days (and nights)... :-(

Thanks for any hints/help on this,
Regards,
Markx

Markx,

This will work for the row field "Name" in a pivottable named PivotTable1

Sub PrintPTMacro()
Dim i As Integer
Dim j As Integer

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
For j = 1 To .PivotItems.Count
If j < i Then .PivotItems(j).Visible = False
Next j
ActiveSheet.PrintOut
MsgBox .PivotItems(i).Name & " is now printing"
Next i
End With
End Sub


HTH,
Bernie
MS Excel MVP


"markx" wrote in message
...
Hi Guys,

Do you know how to print all the pivot items separately (instead of printing the whole pivot
table...)?
Intuitively I think about a macro that will show one pivot item after another then print the page,
but I'm failing to achieve this through VBA coding, since already 3 days (and nights)... :-(

Thanks for any hints/help on this,
Regards,
Markx

Thank you Bernie, works perfectly!
I'll study your code like the Bible... :-)

Regards,
Markx

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Markx,

This will work for the row field "Name" in a pivottable named PivotTable1

Sub PrintPTMacro()
Dim i As Integer
Dim j As Integer

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
For j = 1 To .PivotItems.Count
If j < i Then .PivotItems(j).Visible = False
Next j
ActiveSheet.PrintOut
MsgBox .PivotItems(i).Name & " is now printing"
Next i
End With
End Sub


HTH,
Bernie
MS Excel MVP


"markx" wrote in message
...
Hi Guys,

Do you know how to print all the pivot items separately (instead of
printing the whole pivot table...)?
Intuitively I think about a macro that will show one pivot item after
another then print the page, but I'm failing to achieve this through VBA
coding, since already 3 days (and nights)... :-(

Thanks for any hints/help on this,
Regards,
Markx

Hi,

If I want to save as file instead of print out? How can I do it?

Thanks!

Try this one...

HTH,
Bernie
MS Excel MVP



Sub SavePTMacro()
Dim i As Integer
Dim j As Integer
Dim myB As Workbook

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
For j = 1 To .PivotItems.Count
If j < i Then .PivotItems(j).Visible = False
Next j
ActiveSheet.Cells.Copy
Set myB = Workbooks.Add
myB.Sheets(1).Cells.PasteSpecial xlPasteValues
myB.SaveAs ThisWorkbook.Path & "\" & .PivotItems(i).Name & ".xls"
MsgBox .PivotItems(i).Name & " has been saved to a new file"
myB.Close
Next i
End With
End Sub

wrote in message
...
Hi,

If I want to save as file instead of print out? How can I do it?

Thanks!

On 5$B7n(B5$BF|(B, $B2<8a(B10$B;~(B37$BJ,(B, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Try this one...

HTH,
Bernie
MS Excel MVP

Sub SavePTMacro()
Dim i As Integer
Dim j As Integer
Dim myB As Workbook

With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Name")
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = True
For j = 1 To .PivotItems.Count
If j < i Then .PivotItems(j).Visible = False
Next j
ActiveSheet.Cells.Copy
Set myB = Workbooks.Add
myB.Sheets(1).Cells.PasteSpecial xlPasteValues
myB.SaveAs ThisWorkbook.Path & "\" & .PivotItems(i).Name & ".xls"
MsgBox .PivotItems(i).Name & " has been saved to a new file"
myB.Close
Next i
End With
End Sub

wrote in message

...



Hi,

If I want to save as file instead of print out? How can I do it?

Thanks!- $Bp,i6Ho0zMQJ8;z(B -

- $Bp}<(Ho0zMQJ8;z(B -
Thanks Bernie. It work, but only save one file then show run time
error 1004....

How can same format on Pivottable1 (color, font... ...) when split out
the pivot item?