Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table question | Charts and Charting in Excel | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot Table Question | Excel Discussion (Misc queries) | |||
Pivot Table Question: SubTotals for 2 of 4 Pivot Tables in same worksheet | Excel Programming | |||
Pivot Table Question : If statment in Pivot Table?? | Excel Discussion (Misc queries) |