Pivot Table Chart Number Changes Every Time I Run This Code

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

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Delete first the old table.
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Why do you copy the table from sheets 2?
How many pivot tables do you have?
How many pivot charts do you have?
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Thanks but how do I get my code above to work?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
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")
...
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
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!!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,086
Messages
5,640,014
Members
417,122
Latest member
kirk5370

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
Top