Pivot Table

[Monika]

Hi,

I am trying to create a pivot using fields which will vary
with every month. I use the macro where i have the code
as:

With ActiveSheet.PivotTables("PivotTable3").PivotFields
("Mar-04")
.Orientation = xlDataField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields
("Apr-04")
.Orientation = xlDataField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields
("May-04")
.Orientation = xlDataField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields
("Jun-04")
.Orientation = xlDataField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable3").PivotFields
("Q4 2004")
.Orientation = xlDataField
.Position = 5
End With
ActiveSheet.PivotTables("PivotTable3").PivotFields("Q1
2005").Orientation = _
xlDataField

To solve this problem i am trapping these columns headings
in an array "monArr". and instead of hte above code i
replace the code as:

For i = 1 To totArr
With ActiveSheet.PivotTables
("PivotTable3").PivotFields(monArr(i))
.Orientation = xlDataField
.Position = i
End With

This code is not working. THe values that comes the first
time in the monArr is "3/1/2004". I think this value the
pivot table cannot resolve. How can i solve this problem.
thanks
monika

 

[Dave Peterson]

If monArr() contained dates, then maybe you could use this:

With ActiveSheet.PivotTables("PivotTable3") _
.PivotFields(format(monArr(i), "mmm-yy"))

To get them to be the same format that you want to use.