I have a macro to create a pivot table, I am trying to covert this to a command button for the end user when I attempt to run the command button I receive the runtime error 438 object doesn't support this property or method. The sheet number changes each time the template is used. I want the pivot to read the active workbook Installation wkg always and make a pivot table on a blank sheet to verify totals.
VBA Code:
Cells.Select
Sheets.Add
ActiveWorkbook.Worksheets("Installation wkg").PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Installation wkg!R1C1:R1048576C30", Version:=6).CreatePivotTable _
TableDestination:="Sheet5!R3C1", TableName:="PivotTable1", DefaultVersion _
:=6
Sheets("Sheet5").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1")
.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("PivotTable1").PivotCache
.RefreshOnFileOpen = False
.MissingItemsLimit = xlMissingItemsDefault
End With
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels
Sheets("Sheet5").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Installation Number")
.Orientation = xlColumnField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Current Equip")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Current Equip"), "Count of Current Equip", xlCount
Sheets("Sheet5").Name = "Pivot Count Check"
range("a1").Select
End Sub