gfranco
asked on
Pivot Chart
Hi team,
I am trying to create a pivot chart using a macro, but when I got the result after doing recording macro I got this, but when I was trying to rerun again I got Run-time-error 1004.
Application-defined or object-defined error.
Thanks for your help.
I am trying to create a pivot chart using a macro, but when I got the result after doing recording macro I got this, but when I was trying to rerun again I got Run-time-error 1004.
Application-defined or object-defined error.
Thanks for your help.
Sub Macro1()
'
' Macro1 Macro
'
'
Columns("A:G").Select
Sheets.Add
ActiveWorkbook.Worksheets("Sheet4").PivotTables("PivotTable3").PivotCache. _
CreatePivotTable TableDestination:="Sheet2!R1C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet2").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet2!$A$1:$C$18")
ActiveSheet.Shapes("Chart 1").IncrementLeft 192
ActiveSheet.Shapes("Chart 1").IncrementTop 12.75
Sheets("Sheet2").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data Analysis")
.Orientation = xlRowField
.Position = 1
End With
Sheets("Sheet2").Name = "Scorecard"
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Data Analysis")
.PivotItems("FALSE").Visible = False
.PivotItems("(blank)").Visible = False
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Cycle")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Shipset"), "Count of Shipset", xlCount
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft 181.5
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft 50.25
ActiveSheet.Shapes("Chart 1").IncrementTop -6.75
ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveSheet.Shapes("Chart 1").Line
.Visible = msoTrue
.ForeColor.ObjectThemeColor = msoThemeColorAccent1
.ForeColor.TintAndShade = 0
.ForeColor.Brightness = 0
End With
Range("A2").Select
ActiveSheet.PivotTables("PivotTable1").CompactLayoutRowHeader = _
"Shipment analysis"
Range("B1").Select
ActiveSheet.PivotTables("PivotTable1").CompactLayoutColumnHeader = "Cycle Time"
Range("B2").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ApplyLayout (1)
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.ChartTitle.Select
ActiveChart.ClearToMatchStyle
ActiveChart.ChartStyle = 34
ActiveChart.ClearToMatchStyle
ActiveChart.ChartTitle.Text = "Shipment Process Analysis"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Shipment Process Analysis"
With Selection.Format.TextFrame2.TextRange.Characters(1, 25).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 25).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 18
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
ActiveChart.ChartArea.Select
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Please, just renamed the file as .xlsx
Sheet 4 is the example that I am trying to accomplish.
thanks.
Pivot.txt
Sheet 4 is the example that I am trying to accomplish.
thanks.
Pivot.txt
ASKER
Thanks It went trough
ASKER
Thanks.