Can you make a pivot table by column index as opposed to column name in VBA

Question

I have a table composed of 400 rows and 35 columns, and made a VBA script to make a pivot table comparing 2 columns vs 12 separate columns successfully.

While generating the pivot table, column headers are used (to avoid confusion). I am new with VBA, and this is how I called everything up to my errors

ActiveCell.Select
Range("AA3").Select
Selection.CurrentRegion.Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Practitioners!R3C1:R246C27", Version:=xlPivotTableVersion15). _
    CreatePivotTable TableDestination:="Practitioners!R3C29", TableName:= _
    "PivotTable8", DefaultVersion:=xlPivotTableVersion15
Sheets("Practitioners").Select
Cells(3, 29).Select
ActiveWorkbook.ShowPivotTableFieldList = False
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Capability")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable8").PivotFields("Sub-Capability")
    .Orientation = xlRowField
    .Position = 2
End With
ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
    "PivotTable8").PivotFields("Grade"), "Count of Grade", xlCount
ActiveSheet.PivotTables("PivotTable8").AddDataField ActiveSheet.PivotTables( _
    "PivotTable8").PivotFields("06-Feb-2017"), "Count of 06-Feb-2017", xlCount

but my column headers will change every week, and altering the name of the header means the VBA code will not work because it is no longer "06-Feb-2017".

I tried making a reference to an absolute cell in my code, but came up with an error

Run-time error '1004, Unable to get the PivotFields property of the PivotTable class

Is there a way to refer to the PivotFields as an absolute cell reference (ie B3)?

Shai Rado Shai Rado · Accepted Answer · 2017-02-15T13:14:04

Try the code below (explanations are inside the code comments).

You need to make sure that the formatting of Cell "B3" is identical to the one in the PivotTable, otherwise you will get a run-time error at the following line:

.AddDataField PvtTbl.PivotFields(PvtFlfVal), "Count of " & PvtFlfVal, xlCount

Code

Option Explicit

Sub DynamicPivotTableCountField()

Dim PvtTbl As Pivottable
Dim PvtFlfVal '   As String

' set the Pivot Table, replace "Sheet1" with your sheet's name (don't use ActiveSheet)
Set PvtTbl = Worksheets("Practitioners").PivotTables("PivotTable8")

' get the value from Cell B3, make sure it's formatted the way you need it for the Pivot Table later
PvtFlfVal = Format(Worksheets("Practitioners").Range("B3").Value, "dd-mmm-yyyy")

With PvtTbl
    .AddDataField PvtTbl.PivotFields(PvtFlfVal), "Count of " & PvtFlfVal, xlCount
End With

End Sub

Can you make a pivot table by column index as opposed to column name in VBA

1 Answers