Hello All,
I have the following code:
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?
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