Add all fields to pivot using VBA
 

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

Add all fields to pivot using VBA
 

Have you tried Recording a Macro to see what it provides you with?
Macro Recorder is your friend...

"JimmyA" wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

Add all fields to pivot using VBA
 

The number of columns from C onwards can be variable so recording isn't
appropriate

"JMay" wrote:

Have you tried Recording a Macro to see what it provides you with?
Macro Recorder is your friend...

"JimmyA" wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

Add all fields to pivot using VBA
 

Jmay's suggestion actually is a good one. Even if you can't record the exact
final result you want, you can use the recorder to see the correctly coded
syntax for talking to a pivot table through VBA code. Then modify with some
variables and looping to account for how many columns need to be added, and
you're essentially there. Added bonus of being a great way to build your own
VB skills! :)


"JimmyA" wrote:

The number of columns from C onwards can be variable so recording isn't
appropriate

"JMay" wrote:

Have you tried Recording a Macro to see what it provides you with?
Macro Recorder is your friend...

"JimmyA" wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

Add all fields to pivot using VBA
 

Slarbie/JMay, got a tight deadline on this. I use loops from time to time but
not sure how to pick up the field and set as variable then loop till last
column (within the pivot VB code) . There can be anything from 60 -80
columns. have you got any example loops to pick up the header, add as data
field then move to next column until completed?

Thanks for the quick responses guys :-)

"slarbie" wrote:

Jmay's suggestion actually is a good one. Even if you can't record the exact
final result you want, you can use the recorder to see the correctly coded
syntax for talking to a pivot table through VBA code. Then modify with some
variables and looping to account for how many columns need to be added, and
you're essentially there. Added bonus of being a great way to build your own
VB skills! :)


"JimmyA" wrote:

The number of columns from C onwards can be variable so recording isn't
appropriate

"JMay" wrote:

Have you tried Recording a Macro to see what it provides you with?
Macro Recorder is your friend...

"JimmyA" wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

Add all fields to pivot using VBA
 

I used column A to determine the number of rows that should be included in the
pivottable range. And I used Row 1 to determine the number of columns.

I assume that the headers are all nicely valid--no duplicates and no blanks,
right. And that you want Sum for each of those other fields.

Option Explicit
Sub testme()

Dim myRng As Range
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("A1", .Cells(LastRow, LastCol))

ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"

ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With

With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")

.PivotTables(1).AddFields RowFields:=RowFieldArray

For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol

With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub

It seemed to work ok for me in xl2003.

JimmyA wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

--

Dave Peterson

Add all fields to pivot using VBA
 

I used column A to determine the number of rows that should be included in the
pivottable range. And I used Row 1 to determine the number of columns.

I assume that the headers are all nicely valid--no duplicates and no blanks,
right. And that you want Sum for each of those other fields.

Option Explicit
Sub testme()

Dim myRng As Range
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("A1", .Cells(LastRow, LastCol))

ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"

ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With

With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")

.PivotTables(1).AddFields RowFields:=RowFieldArray

For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol

With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub

It seemed to work ok for me in xl2003.

JimmyA wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

--

Dave Peterson

Add all fields to pivot using VBA
 

Thanks Dave, this is exactly what I need. You rule

"Dave Peterson" wrote:

I used column A to determine the number of rows that should be included in the
pivottable range. And I used Row 1 to determine the number of columns.

I assume that the headers are all nicely valid--no duplicates and no blanks,
right. And that you want Sum for each of those other fields.

Option Explicit
Sub testme()

Dim myRng As Range
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("A1", .Cells(LastRow, LastCol))

ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"

ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With

With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")

.PivotTables(1).AddFields RowFields:=RowFieldArray

For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol

With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub

It seemed to work ok for me in xl2003.

JimmyA wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

--

Dave Peterson

Add all fields to pivot using VBA
 

Thanks Dave, this is exactly what I need. You rule

"Dave Peterson" wrote:

I used column A to determine the number of rows that should be included in the
pivottable range. And I used Row 1 to determine the number of columns.

I assume that the headers are all nicely valid--no duplicates and no blanks,
right. And that you want Sum for each of those other fields.

Option Explicit
Sub testme()

Dim myRng As Range
Dim LastRow As Long
Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim RowFieldArray() As String
Dim DataFieldArray() As String
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
.Activate
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 3 'skipping columns A and B
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("A1", .Cells(LastRow, LastCol))

ReDim RowFieldArray(1 To 3)
RowFieldArray(1) = .Range("a1").Value
RowFieldArray(2) = .Range("b1").Value
RowFieldArray(3) = "Data"

ReDim DataFieldArray(3 To LastCol)
For iCol = 3 To LastCol
DataFieldArray(iCol) = .Cells(1, iCol).Value
Next iCol

.Parent.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=myRng.Address(external:=True)).CreateP ivotTable _
TableDestination:="", _
TableName:="PT" & Format(Now, "yyyymmdd_hhmmss"), _
DefaultVersion:=xlPivotTableVersion10
End With

With ActiveSheet
.PivotTableWizard TableDestination:=.Range("A3")

.PivotTables(1).AddFields RowFields:=RowFieldArray

For iCol = 3 To LastCol
With .PivotTables(1).PivotFields(iCol)
.Orientation = xlDataField
.Position = iCol - 2
.Function = xlSum
End With
Next iCol

With .PivotTables(1).DataPivotField
.Orientation = xlColumnField
.Position = 1
End With

.UsedRange.Columns.AutoFit
.Range("a1").Select 'just to make sure it's visible
.Range("C5").Select
ActiveWindow.FreezePanes = True
End With
End Sub

It seemed to work ok for me in xl2003.

JimmyA wrote:

Hi Guys

I need a macro to create a pivot table for multiple columns. I need column A
and B as Row Fields. Colums C to column x (variable) need to be added
automatically as Pivot fields. The headers for column C to x will be variable.

thanks in advance...

--

Dave Peterson