PDA

View Full Version : Select & drag all fields to Data area in Pivot in one key stroke



rajagopal
03-12-2010, 10:10 AM
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
03-12-2010, 10:34 AM
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

rajagopal
03-14-2010, 10:25 PM
Do i need to place this code in This workbook module?

mdmackillop
03-15-2010, 01:30 AM
Add a Standard module and insert the code in a suitably named Sub routine

rajagopal
03-15-2010, 01:51 AM
I'm getting compile error. would appreciate if you could send me a test file with the code set in it..

GTO
03-15-2010, 02:13 AM
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
03-15-2010, 02:20 AM
Still getting error, can you send me the file which works for you?

GTO
03-15-2010, 02:33 AM
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
03-15-2010, 03:24 AM
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.