Allowing for Variable Pivot Fields in VBA Code

[monicamarie]

I am trying to create a macro in my personal macro workbook with the following code below. The problem is that the header column listed as STATUS_TS(EDT) in the original list sometimes changes to STATUS_TS_GMT. (The file is uploaded daily from another database.) Is there a way to update the code to allow for this possibility?

Any help would be greatly appreciated!

Sub CreatePivotTable()
Dim PTCache As PivotCache
Dim PT As PivotTable

Set PTCache = ActiveWorkbook.PivotCaches.Add _
(SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion.Address)

Set PT = PTCache.CreatePivotTable _
(TableDestination:="", _
TableName:="PivotTable1")

With PT
.PivotFields("STA_CD").Orientation = xlPageField
.PivotFields("ACCT_NUM").Orientation = xlRowField
.PivotFields("TRAN_AMT").Orientation = xlDataField
.PivotFields("STATUS_TS(EDT)").Orientation = xlDataField

Range("A4").Select
ActiveWindow.FreezePanes = True
End With
End Sub

 

[kwrhodes]

Assuming that the number of columns doesn't change, you should be able to replace the hard coded column names with variables (string). Prior to entering this process, run through your column headings and assign the cell values to variables. Then use those variables in place of the column names.

For example:
.PivotFields("STATUS_TS(EDT)").Orientation = xlDataField

Could become
.PivotFields(Column4Heading).Orientation = xlDataField

Column4Heading would contain either of the names you mentioned.

 

[monicamarie]

Hi kwrhodes,
Thank you for your help. As I am very new to VBA, would you be able to tell me how to assign the cell values to variables?

Thank you.

 

[Juan Pablo González]

You could do something like

Column4Heading = Range("A4").Value

or to use a specific worksheet

Column4Heading = Worksheets("My Worksheet").Range("A4").Value

 

[monicamarie]

Thank you both! That worked perfect!!!

 

[G37Sam]

I apologize for such a first post, I have pulled the last hair on my head trying to resolve this but still no luck. It seems so simple but it's driving me nuts.

You could do something like

Column4Heading = Range("A4").Value

or to use a specific worksheet

Column4Heading = Worksheets("My Worksheet").Range("A4").Value

I keep getting the "Application-defined or object-defined error". Here's what I'm doing:

Dim YTDLabel as string, tempYear as long, myPivot as PivotTable
Set myPivot = Sheets("Pivot").PivotTables("PivotTable1")

tempYear = 2012
YTDLabel = "YTD " & tempYear
myPivot.AddDataField myPivot.PivotFields(YTDLabel), tempYear

however if I change that last line to:

myPivot.AddDataField myPivot.PivotFields("YTD 2012"), "2012"

It magically works. Any help would be greatly appreciated.