How to reference Fileld No. instead of Field Name in PivotTable?

[Chirag R Raval]

Dear Sirs,

i have below code for generate pivot table.

Set PCache = ActiveWorkbook.PivotCaches.Create(SourceType:=1, SourceData:=Range("A1").CurrentRegion.address)
    Worksheets.add
    ActiveSheet.name = "Pivot"
    ActiveWindow.DisplayGridlines = False
Set pt = ActiveSheet.PivotTables.add(PivotCache:=PCache, TableDestination:=Range("A1"), TableName:="PivotTable1")
    Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("AR")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY")
        .Orientation = xlRowField
        .Position = 3
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY")
        .Orientation = xlRowField
        .Position = 4
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "UNT"), "Sum of CONF" & Chr(10) & "UNT", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("CONF" & Chr(10) & "QTY"), "Sum of CONF" & Chr(10) & "QTY", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("ORD" & Chr(10) & "VAL"), "Sum of ORD" & Chr(10) & "VAL", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields("AR").LayoutForm = xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("CHNL").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("BUY").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").LayoutForm = _
        xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").Subtotals = Array( _
        False, False, False, False, False, False, False, False, False, False, False, False)
    ActiveSheet.PivotTables("PivotTable1").PivotFields("PARTY").LayoutForm = _
        xlTabular
     
      ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel, True
'ActiveSheet.PivotTables("PivotTable1").TableRange1.Copy

can we reference in code "Filed Number" OR "Column Number" instead of "Field Name"
when generating pivot table ?

because many time, Heading mostly change of particular column like
""PivotTable1").PivotFields("ORD" & Chr(10) & "VAL"), " some time Header is "Order Value" so there are macro stop at that point, i must have change header in sheet as per code.

There are some solution there that we can reference direct column number instead of Field Name?

Regards,

Chirag Raval

 

[Chihiro]

As always uploading a sample would help us in helping you.

 

[Chirag R Raval]

Dear Sir @Chihiro,

Attached an Excel file for your reference with code in it.

Please run.

(1) If header is Different from mentioned in code.


(2)


(3) Change Header As per Code



(4) Success after change Header as per code


if Header can different each time, can we reference Column No instead of
Field Name in Pivot Table Macro?

Also request you to mention some useful websites about Pivot Table Macro .

Regards,

Chirag Raval

 

[Debaser]

Just replace the name with the number.

 

[Chirag R Raval]

Dear Sir @Debaser,

Thank you very much sir for your valuable suggestion , that's perfectly working...
Its seems we can give field no & it is accepted & also notable that its take header name from that cell's value (header name as header row's, That field no's 1st cell).

I have 1 question in mind that can we give filed no from variable?
header rows first cell text like "Ord Val" then if we refer that cell in variable so
if we want top change approach from Field no to variable (because header can be change any time) how can we refer that?

what possibility to optimization? how can be shorten or use variations regarding this references?

please guide & also request ,if you have some useful links for study please mention.

Regards,

Chirag Raval

 

[Debaser]

I have 1 question in mind that can we give filed no from variable?

Yes, it doesn't matter if you use a variable or fixed reference. The variable would need to be a variant so that you can use a number or string.

what possibility to optimization? how can be shorten or use variations regarding this references?

That's pretty vague. What do you mean by 'variations'?

 

[Chirag R Raval]

Dear Sir @Debaser ,

Sorry, for my unclear mentioning..
Variations means , how i explain? shorten type of long object name or shorten reference line in 1 line (like Sir Jb use)

ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(10), "Sum of CONF" & Chr(10) & "UNT", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(11), "Sum of CONF" & Chr(10) & "QTY", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields(13), "Sum of ORD" & Chr(10) & "VAL", xlSum
    ActiveSheet.PivotTables("PivotTable1").PivotFields(1).LayoutForm = xlTabular
    ActiveSheet.PivotTables("PivotTable1").PivotFields(2).LayoutForm = _
        xlTabular

Above code looks very long
shorten means like where need to reference "PivoTable1" we can use just "a"
for "ActiveSheet.PivotTables("PivotTable1").PivotFields(1)" we can use just "B"

How can it optimize? can you give link for useful learning about Pivot Table?

Regards,

Chirag Raval

 

[Debaser]

You've already got a variable for the pivot table, you're just not using it:

    With pt
        With .PivotFields("AR")
            .Orientation = xlRowField
            .Position = 1
        End With
        With .PivotFields("CHNL")
            .Orientation = xlRowField
            .Position = 2
        End With
        With .PivotFields("BUY")
            .Orientation = xlRowField
            .Position = 3
        End With
        With .PivotFields("PARTY")
            .Orientation = xlRowField
            .Position = 4
        End With
        .AddDataField .PivotFields("CONF" & Chr(10) & "UNT"), "Sum of CONF" & Chr(10) & "UNT", xlSum
        .AddDataField .PivotFields("CONF" & Chr(10) & "QTY"), "Sum of CONF" & Chr(10) & "QTY", xlSum
        .AddDataField .PivotFields("ORD" & Chr(10) & "VAL"), "Sum of ORD" & Chr(10) & "VAL", xlSum
        .PivotFields("AR").LayoutForm = xlTabular
        .PivotFields("CHNL").LayoutForm = xlTabular
        .PivotFields("BUY").Subtotals = Array( _
                                        False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("BUY").LayoutForm = xlTabular
        .PivotFields("PARTY").Subtotals = Array( _
                                          False, False, False, False, False, False, False, False, False, False, False, False)
        .PivotFields("PARTY").LayoutForm = xlTabular

        .PivotSelect "", xlDataAndLabel, True
    End With

You should check out contextures.com for lots of tips on pivot tables.