#### zookeepertx

##### Well-known Member

- Joined
- May 27, 2011

- Messages
- 560

- Office Version
- 365

- Platform
- Windows

I'm going to tear my hair out!!! I'm trying to figure out how to get a macro to add Pivot Charts to a workbook and am soon going to wind up in a padded room! I've tried recording a macro doing exactly what I need, but the macro won't work when I try to run it. I've googled all over God's green earth and have tried at least 4 codes that I found there but nothing works. I've tried piecing together code using some/all of the above and NOTHING WORKS!!

Well, actually, I did have one bit of it working on a very small,

I've been working on this for almost 2 days!!

Ultimately, each of the sections of data - separated by "Method" needs to have it's own Pivot Table and Pivot Chart, all on one sheet in the workbook, one below another.

I'm attaching a shortened version of what I'm working with. The first tab is how the data gets here in the very first place. The Results tab is what the coding I've already made does with it (before arriving at the pivot table part). The second "results" is the one I've been using to work on the Pivot Chart Macro. "Pivot Tables" shows what I want to end up with (just using the top 2 sections for an example.) The Pivot Tables need to be grouped by column P - "Method", then by E - "Out of Range". The data consists of Tracking# - First a Count, Second a % of the Grand Total.

Just to be thorough, here is the coding I made to get from the very original data to the Results tabs:

Here is one of the versions of code that I tried to adapt from one I found online.

Here's something else I've tried

I assume nobody wants to slog through the other 5 versions of the code that I have at this point, so I'll save you looking at them.

I'm getting seriously furious with this!! PLEASE can someone help me?

EDITED: Okay, I forgot to attach my workbook to my post and now it won't let me attach via "Edit Post" or "Reply". Why does it hate me??

Jenny

Well, actually, I did have one bit of it working on a very small,

__very__basic example of data, but then, when I tried to make it work on some data just a BIT larger than that, it failed again. Now, I've messed with ALL of the attempts so badly that I can't make sense of any of them.I've been working on this for almost 2 days!!

Ultimately, each of the sections of data - separated by "Method" needs to have it's own Pivot Table and Pivot Chart, all on one sheet in the workbook, one below another.

I'm attaching a shortened version of what I'm working with. The first tab is how the data gets here in the very first place. The Results tab is what the coding I've already made does with it (before arriving at the pivot table part). The second "results" is the one I've been using to work on the Pivot Chart Macro. "Pivot Tables" shows what I want to end up with (just using the top 2 sections for an example.) The Pivot Tables need to be grouped by column P - "Method", then by E - "Out of Range". The data consists of Tracking# - First a Count, Second a % of the Grand Total.

Just to be thorough, here is the coding I made to get from the very original data to the Results tabs:

Code:

```
Sub WebDeliveryDate()
'Jenny 06052015
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
.DisplayAlerts = False
End With
LR = Range("A" & Rows.count).End(xlUp).row
Range("A" & "2" & ":S" & LR).Select
Selection.Sort Key1:=Range("P1"), Order1:=xlAscending
For i = LR To 2 Step -1
If Range("P" & i).Value = "APP" Then
Range("P" & i).Value = "USPS"
ElseIf Range("P" & i).Value = "DD" Then
Range("P" & i).Value = "2 Day"
ElseIf Range("P" & i).Value = "FEDXG" Then
Range("P" & i).Value = "Ground"
ElseIf Range("P" & i).Value = "FEDXH" Then
Range("P" & i).Value = "Home Delivery"
ElseIf Range("P" & i).Value = "ON" Then
Range("P" & i).Value = "Overnight"
Else
Rows(i & ":" & i).EntireRow.Delete
End If
Next i
For LR = Cells(Cells.Rows.count, "P").End(xlUp).row To 3 Step -1
If Cells(LR, "P") <> Cells(LR - 1, "P") Then
Rows(LR).EntireRow.Insert
End If
Next LR
FS = Columns("P").Find(What:=Range("P3").Value, SearchDirection:=xlPrevious).row
SS = Columns("P").Find(What:=Range("P" & FS + 2), SearchDirection:=xlPrevious).row
TS = Columns("P").Find(What:=Range("P" & SS + 2), SearchDirection:=xlPrevious).row
FS2 = Columns("P").Find(What:=Range("P" & TS + 2), SearchDirection:=xlPrevious).row
With Range("A1:S1")
.Copy Range("A" & (FS + 1) & ":S" & (FS + 1) & ",A" & (SS + 1) & ":S" & (SS + 1) & _
",A" & (TS + 1) & ":S" & (TS + 1) & ",A" & (FS2 + 1) & ":S" & (FS2 + 1))
End With
With Application
.ScreenUpdating = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.DisplayAlerts = True
End With
End Sub
```

Here is one of the versions of code that I tried to adapt from one I found online.

Code:

```
Sub CreatePivotTable()
Dim mysheet As Worksheet
Dim pc As PivotCache
Dim pt As PivotTable
Dim pf1 As PivotField
Dim pf2 As PivotField
Dim pf3 As PivotField
Dim pf4 As PivotField
mydata = Range("A1:S6")
Set pc = ActiveWorkbook.PivotCaches.Create(xlDatabase, mydata)
Set pt = pc.CreatePivotTable(Range("A40"), "ItemList")
Set pf1 = pt.PivotFields("Methdo")
pf1.Orientation = xlRowField
Set pf2 = pt.PivotFields("Out of Range")
pf2.Orientation = xlRowField
Set pf3 = pt.PivotFields("Tracking#")
pf3.Orientation = xlDataField
Set pf4 = pt.PivotFields("Tracking#")
pf4.Orientation = xlDataField
pf4.Caption = "% of Method"
pf4.Calculation = xlPercentOfTotal
pf4.NumberFormat = "0.00%"
CreatePivotChart
End Sub
Sub CreatePivotChart()
Dim chobj As ChartObject
Dim ch As Chart
Set mycheet = Sheets("Result(2)")
Set chobj = mycheet.ChartObjects.Add(300, 500, 300, 150)
Set chobj = chobj.Chart
ch.SetSourceData pt.TableRange1
cl.ChartType = xlColumnClustered
chobj.Name = "EChart1"
End Sub
```

Here's something else I've tried

Code:

```
Sub TestPivot()
' TestPivot Macro
Range("A1:S37").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"L29391_289 (5)!R1C1:R37C19", Version:=xlPivotTableVersion14). _
CreatePivotTable TableDestination:="Sheet8!R1C1", TableName:="PivotTable2" _
, DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet8").Select
Cells(1, 1).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Range("Sheet8!$A$1:$C$18")
ActiveSheet.Shapes("Chart 1").IncrementLeft 192
ActiveSheet.Shapes("Chart 1").IncrementTop 15
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Method")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields("OutOfRange")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Tracking#"), "Count of Tracking#", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Tracking#"), "Count of Tracking#2", xlCount
ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Tracking#"). _
Caption = "Count"
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Tracking#2")
.Caption = "% of Method"
.Calculation = xlPercentOfTotal
.NumberFormat = "0.00%"
End With
End Sub
```

I assume nobody wants to slog through the other 5 versions of the code that I have at this point, so I'll save you looking at them.

I'm getting seriously furious with this!! PLEASE can someone help me?

EDITED: Okay, I forgot to attach my workbook to my post and now it won't let me attach via "Edit Post" or "Reply". Why does it hate me??

Jenny

Last edited: