Creating Pivot tables using dynamic columns

**Sakuntala** · 08-05-2009, 08:45 AM

Hi

I am trying to cerate pivot table using a macro.

The columns that I use for poviting are dynamically created, while running the same macro but prior to run this create pivot code, and also the titles change every week.

Can you tell me how to use the dynamic titles in PivotFieds("field name")?

Please see the sample code. it is failing at this point with 1004 error.

With ActiveSheet.PivotTables("PivotTable1").PivotFields(filednamestring)
.Orientation = xlRowField
.Position = 1
End With

Thank you.
Sakuntala

**romperstomper** · 08-05-2009, 08:54 AM

We'd need to see the rest of the code since we don't know how you built the cache or assigned the fieldnamestring (which may or may not contain a typo in your code)

**Sakuntala** · 08-06-2009, 04:13 AM

Hi,

Please see the code below. For testing purpose I have hardcoded the column title in the variable Pvt2ColName. Actually this is the column inserted by another sub, whcich gets executed before this sub.

For the variale Pvt2ColName = "Exceeds August Plan", the month name changes based on the date I run the macro.

The error I am getting is 1004 and stopping at the line

With ActiveSheet.PivotTables("PivotTable2").PivotFields(Pvt2ColName _
)

Sub FYPlanYTDPvt2()
'FYPlanYTDPvt2(PvtRng)
'
' Pvt2 Macro
' Macro recorded 8/5/2009 by schitta

Dim PvtRng As Range
Dim m, n As Integer

Pvt2ColName = "Exceeds August Plan"

Sheets("Report 1").Select
Cells(1, 1).Select
Set PvtRng = Range(Cells(1, 1), Cells(6795, 33))
PvtRng.Select

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
PvtRng).CreatePivotTable TableDestination:="", TableName _
:="PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(5, 1)
ActiveSheet.Cells(5, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Project CMR CC CTO")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields(Pvt2ColName _
)
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Clarity Project ID"), "Count of Clarity Project ID" _
, xlCount
End Sub

**DonkeyOte** · 08-06-2009, 05:30 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Please edit both posts accordingly. Thanks