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.

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

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



.

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

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 -

Hi Tom

Yes the code is written in VBA
I suspect that line wrap in your Newsreader is causing the problem.

I have reproduced the code below, with more forced line breaks so hopefully
your newsreader won't mess it up.

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
' get rid of Sum of before field name
' by adding a space to the source field Name
.Name = fname & " "
.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

"tlee" wrote in message
...
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 -


__________ Information from ESET Smart Security, version of virus
signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 4821 (20100130) __________

The message was checked by ESET Smart Security.

http://www.eset.com