Select & drag all fields to Data area in Pivot in one key stroke

[rajagopal]

Hi,
Issue:

I've 38 field names given in each column. The user wants to calculate the sum only by Pivot report. In such case, the user has to manually drag all the 38 fields in to the Data area.

Requirement:

Is there any other way in which the user can select and drag all the 38 fields in the Data area in one key stroke?

Please find attached the data and help me.

[mdmackillop]

[VBA]Dim i

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Raw data'!R1C1:R34C39").CreatePivotTable TableDestination:= _
"'[Pivot_Select_drag_in one key stroke.xls]Raw data'!R39C23", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
For i = 1 To 38

With ActiveSheet.PivotTables("PivotTable1").PivotFields("A" & i)
.Orientation = xlDataField
End With
Next[/VBA]

[rajagopal]

Do i need to place this code in This workbook module?

[mdmackillop]

Add a Standard module and insert the code in a suitably named Sub routine

[rajagopal]

I'm getting compile error. would appreciate if you could send me a test file with the code set in it..

[GTO]

Hi there,

I know zip about pivots, but if you'll add the rest of the underscores to the wb's name, ie - "'[Pivot_Select_drag_in_one_key_stroke.xls]Raw data'!R39C23", I believe it works as desired.

Mark

[rajagopal]

Still getting error, can you send me the file which works for you?

[GTO]

I am afraid I would not be emailing if that is what you mean. Here is your wb with the code installed as MD directed.

[mdmackillop]

This works for me, but it does seem "delicate". You may want to record your own table creation macro and add the looping code to it.