I need to make a pivot table with a macro. I cannot use XL2BB but I am attaching a screenshot of my data. When I run the macro, I get an error on the "With ActiveSheet.PivotTables("pTable1")" line. Any help is appreciated. If there is another way to provide sample data, I would gladly do so.
Code:
Sub PivotTable()
'
' Macro1 Macro
'
'
Sheets("Payroll Data").Select
Dim rng1 As Range
Dim sht1 As Worksheet
Dim pTable1 As PivotTable
Set rng1 = ActiveSheet.Cells(1, 1).CurrentRegion
Set sht1 = ActiveWorkbook.Worksheets.Add
sht1.Name = "Pivot Table"
Set pTable1 = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
rng1.Address, Version:=8).CreatePivotTable(TableDestination:= _
sht1.Cells(1, 1), TableName:="PivotTable" & Format(Time, "hhmmss"))
Sheets("Pivot Table").Select
Cells(2, 2).Select
With ActiveSheet.PivotTables("pTable1")
.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("pTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("pTable1").RepeatAllLabels xlRepeatLabels
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee_Number")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("pTable1").PivotFields("CC1")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("pTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Regular_Hours"), "Sum of Regular_Hours", xlSum
End Sub