[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1001
  • Last Modified:

VBA to Print Charts, Error 1004 Unable to set Default Property of Pivot Item Class

I am running into an error when trying to execute this macro.  The error occurs at the following line of code:
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
I recieve runtime error '1004": Unable to set the Default property of the pivot class.

How do I resolve this?
Sub PrintPivotCharts()
 'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable
  For Each pf In pt.PageFields
    For Each pi In pf.PivotItems
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
'      ActiveSheet.PrintOut
      ActiveSheet.PrintPreview
      'print preview for testing
    Next
  Next pf
End Sub

Open in new window

0
kreepa
Asked:
kreepa
  • 4
  • 3
1 Solution
 
jeveristCommented:
Hi kreepa,

Hmm... It works fine for me with an active PivotChart.  Are you sure you have a PivotChart (with a Page field) that is active?

Jim
0
 
kreepaAuthor Commented:
I believe so. There is a page field named "Resources". This is the page that should be scrolled through and printed.
0
 
jeveristCommented:
kreepa,

Yeah, it looks like you have some old, unused PivotItems in there that need to be deleted.  Try this:

Sub PrintPivotCharts()
 'prints a chart for each item in the page field
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Set pt = ActiveChart.PivotLayout.PivotTable

  For Each pf In pt.PageFields
 
    On Error Resume Next
    For Each pi In pf.PivotItems
        pi.Delete
    Next pi
    On Error GoTo 0
   
    pt.RefreshTable
   
    For Each pi In pf.PivotItems
      pt.PivotFields(pf.Name).CurrentPage = pi.Name
'      ActiveSheet.PrintOut
      ActiveSheet.PrintPreview
      'print preview for testing
    Next
  Next pf
End Sub

Jim
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
kreepaAuthor Commented:
My pivot contains data in the following areas:

Page = Resource (About 20 items)
Column = Options (ie Active, Approved, MIN, MAX) Regarding Each Resourrce
Data = Months (13 months) or so
Row is Blank
0
 
kreepaAuthor Commented:
Excellent!!!!!!!!!!
0
 
kreepaAuthor Commented:
Fast and effective. I appreciate your time. I was banging my head against the desk for two hours and you were able to complete this in minutes.
0
 
jeveristCommented:
kreepa,

Glad to help and thanks for the grade!

Jim
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now