VBA shift cells down, apply pivot to Active sheet

Question

I recorded this macro that shifts all data 75 rows down, selects however much data I have (# of rows varies, but # of columns is fixed) and inserts a pivot table at A76 of the same sheet (with a particular set of pivot table formatting). It works on the first sheet, but continues to error out due to the sheet references/cell references on subsequent sheets. Any help on this would be great, thanks!

Sub PivotCurrentSheet()

'shift down cells
Rows("1:75").Select
Selection.Insert Shift:=xlDown
Range("A76").Select

'create pivot table
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R76C1:R86C22", Version:=6).CreatePivotTable TableDestination:= _
    "Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:=6
Sheets("Sheet1").Select
Cells(1, 1).Select

'column selections
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Account No")
    .Orientation = xlColumnField
    .Position = 1
End With

'data selections
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Txn Amount"), "Sum of Txn Amount", xlSum

'row selections
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Opportunity")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Order Number")
    .Orientation = xlRowField
    .Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Project ID")
    .Orientation = xlRowField
    .Position = 3
End With

'No subtotals
ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch Posting Date"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Entry Date").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Batch Description"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Memo").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Document Reference"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Document Number"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer ID").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Journal").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Account No").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Txn Amount").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Transaction Currency"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Debit Amount").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Credit Amount").Subtotals _
    = Array(False, False, False, False, False, False, False, False, False, False, False, False _
    )
ActiveSheet.PivotTables("PivotTable1").PivotFields("Line No").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Item ID").Subtotals = Array _
    (False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Location Name").Subtotals _
    = Array(False, False, False, False, False, False, False, False, False, False, False, False _
    )
ActiveSheet.PivotTables("PivotTable1").PivotFields("Department ID").Subtotals _
    = Array(False, False, False, False, False, False, False, False, False, False, False, False _
    )
ActiveSheet.PivotTables("PivotTable1").PivotFields("Record Type").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Customer Name").Subtotals _
    = Array(False, False, False, False, False, False, False, False, False, False, False, False _
    )
ActiveSheet.PivotTables("PivotTable1").PivotFields("Opportunity").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales Order Number"). _
    Subtotals = Array(False, False, False, False, False, False, False, False, False, False, _
    False, False)
ActiveSheet.PivotTables("PivotTable1").PivotFields("Project ID").Subtotals = _
    Array(False, False, False, False, False, False, False, False, False, False, False, False)

'repeat item labels
ActiveSheet.PivotTables("PivotTable1").RepeatAllLabels xlRepeatLabels

'tabular format
ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlTabularRow  End Sub

I highly suggest reading through [How to Avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Do you have all of those pivot fields in all of the sheets you want to run this on? Also, note the `SourceData` in the first Create Pivot Table explicitly references`Sheet1`. Do you need to change/update that for other sheets? — BruceWayne, Aug 07 '17 at 16:51
All of the sheets are saved in the workbook, and the fields will be the same. I am attempting to apply the same pivot table to any sheet, so the sheet name and cell references will need to be variable. Thank you for sharing that link. — bmpeterson1, Aug 07 '17 at 17:10
maybe start by cleaning up your code a little bit. ... assign the `Array(false, false ....` to a variable and use the variable instead of repeating all the arrays .... take out `Sheets("Sheet1").Select Cells(1, 1).Select` ... it does nothing ... except select A1 — jsotola, Aug 07 '17 at 21:11

VBA shift cells down, apply pivot to Active sheet

0 Answers0