Using array for cleaner code

Hi,

I am using a macro to consolidate data from multiple sheet in a workbook. It all works fine but a portion of the code is too long. I am new to all this but understand that array can be used to have a cleaner code. Can someone please help me on how I can shorten the code below using array? Thanks much in advance.

.AddDataField .PivotFields(26), "Jan", xlSum
.AddDataField .PivotFields(27), "Feb", xlSum
.AddDataField .PivotFields(28), "Mar", xlSum
.AddDataField .PivotFields(29), "Apr", xlSum
.AddDataField .PivotFields(30), "May", xlSum
.AddDataField .PivotFields(31), "Jun", xlSum
.AddDataField .PivotFields(32), "Jul", xlSum
.AddDataField .PivotFields(33), "Aug", xlSum
.AddDataField .PivotFields(34), "Sep", xlSum
.AddDataField .PivotFields(35), "Oct", xlSum
.AddDataField .PivotFields(36), "Nov", xlSum
.AddDataField .PivotFields(37), "Dec", xlSum

With .PivotFields( _
"Jan")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Feb")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Mar")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Apr")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"May")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Jun")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Jul")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Aug")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Sep")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Oct")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Nov")
.NumberFormat = "###,0"
End With

With .PivotFields( _
"Dec")
.NumberFormat = "###,0"
End With

Hi Japan,

Thanks much for the solution. That worked out nicely.

One more question is what changes do I need to make to the code you provided to include a non-contiguous column? For example, field number 15 and Caption is "My Subs" like below. Of course, the non-contiguous one will have the same number format as others.

.AddDataField .PivotFields(15), "My Subs", xlSum
.AddDataField .PivotFields(26), "Jan", xlSum
.AddDataField .PivotFields(27), "Feb", xlSum
.AddDataField .PivotFields(28), "Mar", xlSum
.
.
.
.

Raj

Thanks Japan.

Is there any way to create two arrays (one for the .PivotFields and one for captions and make this work? Just thinking out loud. If that can be done, it would be wonderful and I can take you for a beer!

Hi Japan,

I haven't given your latest solution a try but if we have more than one non-adjacent column which we are trying to pull data from, can we somehow use "Case" somewhere in the code above? Sorry to keep bugging you but trying to see if you might have a solution. Thanks.

Raj