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
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
delete:
ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
"myPivotTable").PivotFields("DOB"), "Count of DOB", xlCount
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
No problem thanks for your help so far.

I cannot understand why it is still sopping on the Months field.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Me too. I had to start Excel a few times again. Maybe you can do that also.
Do you delete the existing pivottable before you start the macro?
In your macro I can't see the macro for deleting pivottables.
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
I will try and reboot.

I do not want to delete the pivot tables because I have to copy the pivot tables from Sheet 2.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,096
Messages
5,640,087
Members
417,126
Latest member
Jeffman52

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