Convert Marco to Command button to run a pivot table in a template

fvisions

Board Regular
Joined
Jul 29, 2008
Messages
174
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,113,979
Messages
5,545,316
Members
410,676
Latest member
M0J0jojo
Top