In the following code I am trying to declare a Chart variable named myChart.
Do I need to declare a Dim Statement at the top of the sub routine or d I need to do something else?
The code works perfectly until it reaches this line:
ActiveSheet.Shapes("myChart").IncrementLeft 372.75
It starts to create the chart and then stops.
Do I need to declare a Dim Statement at the top of the sub routine or d I need to do something else?
VBA Code:
Sub Create_Pivot_Chart_Month_And_Names_3()
'
Sheets("2").Select
Range("Table9[#All]").Select
Selection.Copy
Sheets("5b").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table9", Version:=6).CreatePivotTable TableDestination:="5b!R1C6", _
TableName:="myPivotTable", DefaultVersion:=6
Sheets("5b").Select
Cells(1, 6).Select
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
With ActiveSheet.PivotTables("myPivotTable").PivotFields("Name")
.Orientation = xlRowField
.Position = 1
End With
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
ActiveSheet.PivotTables("myPivotTable").AddDataField ActiveSheet.PivotTables( _
"myPivotTable").PivotFields("DOB"), "Count of DOB", xlCount
Range("F2").Select
ActiveSheet.PivotTables("myPivotTable").PivotFields("Name").PivotFilters.Add2 _
Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
"myPivotTable").PivotFields("Count of DOB"), Value1:=2
Range("F3").Select
ActiveSheet.PivotTables("myPivotTable").PivotFields("DOB").PivotFilters.Add2 _
Type:=xlValueIsGreaterThanOrEqualTo, DataField:=ActiveSheet.PivotTables( _
"myPivotTable").PivotFields("Count of DOB"), Value1:=2
ActiveSheet.Shapes.AddChart2(251, xlPie).Select
ActiveChart.SetSourceData Source:=Range("'5b'!$F$1:$G$12")
ActiveWorkbook.ShowPivotTableFieldList = False
'ActiveSheet.Shapes("Chart 7").IncrementLeft 372.75
ActiveSheet.Shapes("myChart").IncrementLeft 372.75
'ActiveSheet.Shapes("Chart 7").IncrementTop -54.75
ActiveSheet.Shapes("myChart").IncrementTop -54.75
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
Application.CommandBars("Format Object").Visible = False
Selection.Format.TextFrame2.TextRange.Font.Bold = msoTrue
ActiveChart.ChartTitle.Text = "Names Occuring More Than Once" & Chr(13) & "In The Same Month"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Names Occuring More Than Once" & Chr(13) & "In The Same Month"
With Selection.Format.TextFrame2.TextRange.Characters(1, 30).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, 25).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(31, 17).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(31, 17).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 SubThe code works perfectly until it reaches this line:
ActiveSheet.Shapes("myChart").IncrementLeft 372.75
It starts to create the chart and then stops.
