Updating Recorded Macro for Pivot Table to Work for Various Data Sets

[cash1257]

*I have VERY limited experience with coding*

I am trying to use VBA to insert a pivot table. I am having trouble modifying the recorded macro adding the pivot table. When I try to repeat the macro, I am getting an error "Runtime error 1004"


My table has the following columns (starting at A5):

Maintainer

Territory ID

Trouble Code

Trouble Ticket#

Trouble Description

Affected Resource /DOT#

Subdivision

Ticket Description Summary

Line Seg

Mile Post

Start Date

<tbody>
</tbody>

In the pivot table, I need:
-"Maintainer" set as a filter
-"Mile Post" set as a column
-"Trouble Code" set as a row
-Sum of Mile Post as the value

When I generate the recorded Macro of adding the pivot table, I get the following:

Sub Macro1()

Sheets.Add
ActiveWorkbook.Worksheets("Sheet5").PivotTables("PivotTable22").PivotCache. _
CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
, DefaultVersion:=xlPivotTableVersion15
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("" & Chr(10) & "" & Chr(10) & "" & Chr(10) & "Maintainer")
.Orientation = xlPageField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("" & Chr(10) & "" & Chr(10) & "Mile" & Chr(10) & "Post")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("" & Chr(10) & "" & Chr(10) & "Mile" & Chr(10) & "Post"), "Count of " & Chr(10) & "" & Chr(10) & "Mile" & Chr(10) & "Post", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("" & Chr(10) & "" & Chr(10) & "Trouble" & Chr(10) & "Code")
.Orientation = xlRowField
.Position = 1
End With
End Sub




I have tried for hours to fix this. Any help would be appreciated!

 

[iliace]

Are you building a PivotTable from an existing PivotTable? What is 'Sheet5' in your workbook?

If you are not trying to build from an existing PivotTable, can you provide some additional information about your data source? For example, your table starting in A5, what sheet is it in?

I wrote some code that will probably make your life easier, but just need those extra pieces of information.