Is the code VBA?
It does not seem to compile in VBA. The := outside the scope of a
paramter appears to fail
e.g. using TableDestination:="" as an assignment.
Sorry is this is a silly question. Tom

On Jan 2, 6:13*pm, "Roger Govier" <roger@technology4unospamdotcodotuk
wrote:
Sorry, no such feature available.
Code is the only way to automate the procedure

--
Regards
Roger Govier

"Johnny_99" wrote in message

...





Thanks Roger.

I presume this is not a nonprogramming solution here? I'd love to simply
select column 1, ctrl, select column n (and take the range of columns) and
add them all ... perhaps asking too much?

No prcedure or add-in solution?

Thanks,

"Roger Govier" wrote:

Hi Johnny

The following code should get you started on what you want to do.
In this example the first column of source data is added to Page area,
the
second column is added to the Row area then there is a loop to add 60
columns to the Data area, ensuring that each is set to Sum and getting
rid
of the annoying "Sum of " which has to appear before each field name, by
appending a space to the original Field name for use in the PT.

Sub CreatePivot()
* * Dim wss As Worksheet, wsd As Worksheet
* * Dim i As Long, j As Long, fname As String

* * Set wss = Sheets("Sheet1")
* * Application.ScreenUpdating = False
* * Application.Calculation = xlCalculationManual

* * ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _

wss.Range("A1:CZ20000")).CreatePivotTable
TableDestination:="", TableName:= _
* * * * * * * * * * * * * * * * * *"PivotTable1",
DefaultVersion:=xlPivotTableVersion10
* * Set wsd = ActiveSheet
* * wsd.PivotTableWizard TableDestination:=wsd.Cells(3, 1)

* * wsd.Cells(3, 1).Select
* * ' in this case the first 2 columns of source data have been added to
* * ' Row field and Page field respectively
* * wsd.PivotTables("PivotTable1").AddFields RowFields:=Array("Date", _
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * "Data"),
PageFields:="Name"

* * j = 2 *' set the start column as 1 less than where you wish to pick
* * * * * * *' up data fields from
* * For i = 1 To 60
* * * * * * * * ' loop for 60 columns to add fields to the data area
* * * * * * * * ' picking up the field name from the column header
* * * * fname = wss.Cells(1, j + i).Value
* * * * With wsd.PivotTables("PivotTable1").PivotFields(fname)
* * * * * * .Orientation = xlDataField
* * * * * * .Function = xlSum * * * * * * * ' force a Sum
* * * * * * .Name = fname & " " * * * * *' get rid of Sum of before field
name
* * * * * * * * * * * * * * * * * * * * * * * * * * * ' by appending a
space
to the source field Name
* * * * * * .Position = i
* * * * End With
* * Next

* * ' next part allocates the 60 data fields across columns instead
* * ' of appearing under each other (if that is what is required)

* * With ActiveSheet.PivotTables("PivotTable1").DataPivotFi eld
* * * * .Orientation = xlColumnField
* * * * .Position = 1
* * End With

* * Application.ScreenUpdating = True
* * Application.Calculation = xlCalculationAutomatic
End Sub

--
Regards
Roger Govier

"Johnny_99" wrote in message
...
As with a previous question, I have a large set of data (20,000 rows
and
about 100 coulmns). I wish to select about 60 columns to place in pivot
"data" (along with others into "Rows").

Is there a way to select multiple columns (say all 60?) and move into
"Data"
in one step? Are there add-ins that help with this? Doing all 60 is
possible
but slow and somewhat error prone.

Thanks in advance.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

.

__________ Information from ESET Smart Security, version of virus
signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4738 (20100102) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -