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



.