Pivot Table Chart Number Changes Every Time I Run This Code

K

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Hello All,

I have the following code:

VBA Code: 
Sub Create_Pivot_Chart_2a()
'
' Create_Pivot_Chart_2a Macro
'

'
    Sheets("2").Select
    Range("Table9[#All]").Select
    Selection.Copy
    Sheets("5a").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table978", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="PivotTable5", DefaultVersion:=6
    Sheets("5a").Select
    Cells(3, 6).Select
    With ActiveSheet.PivotTables("PivotTable5")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("PivotTable5").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("PivotTable5").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Name"), "Count of Name", xlCount
    With ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB")
        .Orientation = xlRowField
        '.Position = 2
    End With
    ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB").AutoGroup
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Quarters").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("PivotTable5").AddDataField ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("DOB"), "Count of DOB", xlCount
    Range("F4").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Name").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Count of Name"), Value1:=2
    Range("F5").Select
    ActiveSheet.PivotTables("PivotTable5").PivotFields("DOB").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "PivotTable5").PivotFields("Count of Name"), Value1:=2
    ActiveSheet.Shapes.AddChart2(251, xlPie).Select
    ActiveChart.SetSourceData Source:=Range("'5a'!$F$3:$H$14")
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.Shapes("Chart 10").IncrementLeft 381.75
    ActiveSheet.Shapes("Chart 10").IncrementTop -61.5
    ActiveChart.ChartTitle.Select
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.ChartTitle.Text = "Names Occuring More Than Once In The Same Month"
    Selection.Format.TextFrame2.TextRange.Characters.Text = _
        "Names Occuring More Than Once In The Same Month"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 47).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(6, 42).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True
    Selection.ShowCategoryName = True
    Application.CommandBars("Format Object").Visible = False
    ActiveWindow.SmallScroll Down:=-15
    Range("E1").Select
End Sub

My code stops every time in this section.

How do I get the code to acount for a change in the name of the pivot chart and/or pivot table in this part of the code?

VBA Code: 
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table978", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="PivotTable5", DefaultVersion:=6
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
I use the same pivot table data every time from the same worksheet.

That kind of make sense now so when I copy the pivot table to the new worksheet do I need to rename it every time in the code?

Do you have or any else have any examples of looping and check to see if a pivot table name previously exists?
 
Upvote 0
Every time I run the code now the Chart number automatically increments.

VBA Code: 
 ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveSheet.Shapes("Chart 12").IncrementLeft 381.75
    ActiveSheet.Shapes("Chart 12").IncrementTop -61.5
    ActiveChart.ChartTitle.Select
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.ChartTitle.Text = "Names Occuring More Than Once In The Same Month"

In this case the next time I would run all of the code it would stop on this line:

VBA Code: 
ActiveSheet.Shapes("Chart 12").IncrementLeft 381.75

Since the chart number changes.

How do I fix this?
 
Upvote 0
Why do you copy the table from sheets 2?
How many pivot tables do you have?
How many pivot charts do you have?
 
Upvote 0
I have one Pivot Table on sheet 2.

I have 3 different charts on 3 different sheets.

The first chart is on Sheet 5, then 5a and then 5b.

I use the data from the table in Sheet 2 to create the pivot table.

Hope this info helps and thank you for reading.
 
Upvote 0
This macro delete all your pivottables.

VBA Code: 
Sub DeletePivotTablesWorkbook()
Dim Pt As PivotTable
Dim sh As Worksheet
For Each sh In Sheets
    For Each Pt In sh.PivotTables
        Pt.TableRange2.Clear
    Next Pt
Next sh
End Sub
 
Upvote 0
When you call this macro in your macro the pivot table can be created whitout errors.

VBA Code: 
Sub Create_Pivot_Chart_2a()
'
' Create_Pivot_Chart_2a Macro
'

'
    DeletePivotTablesWorkbook
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table9", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="myPivotTable", DefaultVersion:=6
    Sheets("5a").Select
    With ActiveSheet.PivotTables("myPivotTable")
...
 
Upvote 0
Thanks I was starting to think I had to declare a variable to keep it from constantly change the pivot table name.

I am slowly but surely getting there.

I gather I can do the same thing with the Pivot Chart?

Here is the code now:

VBA Code: 
Sub Create_Pivot_Chart_3()
'
' Create_Pivot_Chart_3 Macro
'

'
    Sheets("2").Select
    Range("Table9[#All]").Select
    Selection.Copy
    Sheets("5a").Select
    Range("A1").Select
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Table9", Version:=6).CreatePivotTable TableDestination:="5a!R1C6", _
        TableName:="myPivotTable", DefaultVersion:=6
        Sheets("5a").Select
    With ActiveSheet.PivotTables("myPivotTable")
    ActiveSheet.Paste
    Application.CutCopyMode = False
    'ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    '    "Table938", Version:=6).CreatePivotTable TableDestination:="5a!R2C6", _
    '    TableName:="myPivotTable", DefaultVersion:=6
    Sheets("5a").Select
    Cells(2, 6).Select
    End With
    With ActiveSheet.PivotTables("myPivotTable")
        .ColumnGrand = True
        .HasAutoFormat = True
        .DisplayErrorString = False
        .DisplayNullString = True
        .EnableDrilldown = True
        .ErrorString = ""
        .MergeLabels = False
        .NullString = ""
        .PageFieldOrder = 2
        .PageFieldWrapCount = 0
        .PreserveFormatting = True
        .RowGrand = True
        .SaveData = True
        .PrintTitles = False
        .RepeatItemsOnEachPrintedPage = True
        .TotalsAnnotation = False
        .CompactRowIndent = 1
        .InGridDropZones = False
        .DisplayFieldCaptions = True
        .DisplayMemberPropertyTooltips = False
        .DisplayContextTooltips = True
        .ShowDrillIndicators = True
        .PrintDrillIndicators = False
        .AllowMultipleFilters = False
        .SortUsingCustomLists = True
        .FieldListSortAscending = False
        .ShowValuesRow = False
        .CalculatedMembersInFilters = False
        .RowAxisLayout xlCompactRow
    End With
    With ActiveSheet.PivotTables("myPivotTable").PivotCache
        .RefreshOnFileOpen = False
        .MissingItemsLimit = xlMissingItemsDefault
    End With
    ActiveSheet.PivotTables("myPivotTable").RepeatAllLabels xlRepeatLabels
    ActiveWorkbook.ShowPivotTableFieldList = True
    ActiveWindow.SmallScroll Down:=-63
    With ActiveSheet.PivotTables("myPivotTable").PivotFields("Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
        "myPivotTable").PivotFields("Name"), "Count of Name", xlCount
    ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
        "myPivotTable").PivotFields("DOB"), "Count of DOB", xlCount
    With ActiveSheet.PivotTables("myPivotTable").PivotFields("DOB")
        .Orientation = xlRowField
        .Position = 2
    End With
    ActiveSheet.PivotTables("myPivotTable").PivotFields("DOB").AutoGroup
    ActiveSheet.PivotTables("myPivotTable").PivotFields("Years").Orientation = _
        xlHidden
    ActiveSheet.PivotTables("myPivotTable").PivotFields("Quarters").Orientation = _
        xlHidden
    Range("F3").Select
    ActiveSheet.PivotTables("myPivotTable").PivotFields("Name").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "myPivotTable").PivotFields("Count of Name"), Value1:=2
    Range("F4").Select
    ActiveSheet.PivotTables("myPivotTable").PivotFields("DOB").PivotFilters.Add2 _
        Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
        "myPivotTable").PivotFields("Count of Name"), Value1:=2
    ActiveSheet.Shapes.AddChart2(251, xlPie).Select
    ActiveChart.SetSourceData Source:=Range("'5a'!$F$2:$H$13")
    ActiveSheet.Shapes("Chart 21").IncrementLeft 379.5
    ActiveSheet.Shapes("Chart 21").IncrementTop -60
    ActiveWorkbook.ShowPivotTableFieldList = False
    ActiveChart.FullSeriesCollection(1).Select
    ActiveChart.FullSeriesCollection(1).ApplyDataLabels
    ActiveChart.FullSeriesCollection(1).DataLabels.Select
    Selection.ShowPercentage = True
    Selection.ShowCategoryName = True
    Application.CommandBars("Format Object").Visible = False
    ActiveChart.ChartArea.Select
    ActiveChart.ChartTitle.Select
    ActiveChart.ChartTitle.Text = "Names Occuring More Than" & Chr(13) & "Once In The Same Month"
    Selection.Format.TextFrame2.TextRange.Characters.Text = _
        "Names Occuring More Than" & Chr(13) & "Once In The Same Month"
    With Selection.Format.TextFrame2.TextRange.Characters(1, 25).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 5).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(6, 20).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(26, 22).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
    With Selection.Format.TextFrame2.TextRange.Characters(26, 22).Font
        .BaselineOffset = 0
        .Bold = msoFalse
        .NameComplexScript = "+mn-cs"
        .NameFarEast = "+mn-ea"
        .Fill.Visible = msoTrue
        .Fill.ForeColor.RGB = RGB(89, 89, 89)
        .Fill.Transparency = 0
        .Fill.Solid
        .Size = 14
        .Italic = msoFalse
        .Kerning = 12
        .Name = "+mn-lt"
        .UnderlineStyle = msoNoUnderline
        .Spacing = 0
        .Strike = msoNoStrike
    End With
    ActiveChart.ChartArea.Select
    Range("E1").Select
End Sub

Now it is stopping on this line:

With ActiveSheet.PivotTables("myPivotTable").PivotFields("DOB")
.Orientation = xlRowField
.Position = 2

Thanks for shedding some light on the first part!!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question
This Macro doesn't add vertical axes title
Replies
0
Views
285
yabi100
Y
S
  • Question
Excel VBA Getting error when creating pivot chart but error only occurs after sub that creates pivot tables. No error if directly called
Replies
0
Views
638
Steve48
S
W
  • Question
Run time error '424'
2
Replies
12
Views
879
Domenic
D
K
  • Question
Tring to declare a Pivot Chart Variable
Replies
6
Views
573
RoryA
RoryA
S
  • Question
Why won't a macro for a pivot table run on an identical workbook?
Replies
0
Views
614
superbuttz
S

Forum statistics

Threads
1,214,904
Messages
6,122,169
Members
449,070
Latest member
webster33

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top