Pivot Table VBA question
 

I recorded a macro of creating a new PivotTable (XL 2007), adding a couple
of fields, and the following was recorded. I added just one line (commented
below), to assure that the sheet created when the macro was played would
have the same name as when the macro was recorded. Then, i deleted the
sheet that was created when the macro was recorded, so i could test running
the macro (in other words, workbook now back to what it was like when the
macro was recorded).

When i recorded the macro, the resultant PivotTable has one row field and
one data field, as you would expect reading the recorded code.

When i play back the code, the resultant PivotTable only has a DataField, in
other words it is a one-cell PivotTable. In the playback, the row field
disappears. In fact, i can see this if i step thru it during playback and
look at the sheet. After the RowField is added, i see it on the PivotTable.
But, after the AddDataField method is finished, the RowField is removed, and
only a DataField remains.

Does anybody know what is going on here? Is this a BUG? I just want to be
able to play back the macro and have it work.

Thanks, Tom

Sub Macro5()
'
' Macro5 Macro
'

'
Sheets.Add
'Added this line to assure constant sheet name
ActiveSheet.Name = "Sheet20"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"20070409!R1C1:R101C45",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet20!R3C1", TableName:="PivotTable11", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet20").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable11").PivotField s("Financial
Strength")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable11").AddDataFie ld
ActiveSheet.PivotTables( _
"PivotTable11").PivotFields("Financial Strength"), _
"Count of Financial Strength", xlCount
End Sub

Pivot Table VBA question
 

Switch the order so the data field is added before the Row field, and it
should work correctly:

ActiveSheet.PivotTables("PivotTable11").AddDataFie ld _
ActiveSheet.PivotTables("PivotTable11") _
.PivotFields("Financial Strength"), _
"Count of Financial Strength", xlCount
With ActiveSheet.PivotTables("PivotTable11") _
.PivotFields("Financial Strength")
.Orientation = xlRowField
.Position = 1
End With



TomCon wrote:
I recorded a macro of creating a new PivotTable (XL 2007), adding a couple
of fields, and the following was recorded. I added just one line (commented
below), to assure that the sheet created when the macro was played would
have the same name as when the macro was recorded. Then, i deleted the
sheet that was created when the macro was recorded, so i could test running
the macro (in other words, workbook now back to what it was like when the
macro was recorded).

When i recorded the macro, the resultant PivotTable has one row field and
one data field, as you would expect reading the recorded code.

When i play back the code, the resultant PivotTable only has a DataField, in
other words it is a one-cell PivotTable. In the playback, the row field
disappears. In fact, i can see this if i step thru it during playback and
look at the sheet. After the RowField is added, i see it on the PivotTable.
But, after the AddDataField method is finished, the RowField is removed, and
only a DataField remains.

Does anybody know what is going on here? Is this a BUG? I just want to be
able to play back the macro and have it work.

Thanks, Tom

Sub Macro5()
'
' Macro5 Macro
'

'
Sheets.Add
'Added this line to assure constant sheet name
ActiveSheet.Name = "Sheet20"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _
"20070409!R1C1:R101C45",
Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet20!R3C1", TableName:="PivotTable11", _
DefaultVersion:=xlPivotTableVersion10
Sheets("Sheet20").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable11").PivotField s("Financial
Strength")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable11").AddDataFie ld
ActiveSheet.PivotTables( _
"PivotTable11").PivotFields("Financial Strength"), _
"Count of Financial Strength", xlCount
End Sub




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html