Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for pivot tables
What I am trying to do is to use VBA to create a pivot table and place
it in a particular named sheet. Playing with recording pivot table creation and tweaking that slightly I have got to Dim strPivotName As String strPivotName = "Pivot" ActiveWorkbook.Sheets.Add().Name = strPivotName ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Range("a1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _ "PivotResults", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=Sheets(strPivotName).Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotResults").AddFields RowFields:="Code", _ ColumnFields:="Week" With ActiveSheet.PivotTables("PivotResults").PivotField s("Q") .Orientation = xlDataField .Caption = "Sum of Q" .Function = xlSum End With Apart from being incredibly ugly code this nearly does what I want. It creates the pivot table and puts it in the appropriately named sheet. However it also create a spurious blank sheet. I suspect this is something to do with the TableDestination:="" but replacing that with TableDestination:=Sheets(strPivotName causes an error. What am I doing wrong? -- Each day a man watched a donkey walk past a high wood fence with one plank removed. Each day he saw a nose, then the ears, then the neck, forequarters, back and finally the tail. He pondered this for a time and eventually declared. “I understand now. The nose causes the tail” |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for pivot tables
You can create a string with the sheet name and destination cell, e.g.:
'==================== Sub test() Dim strPivotName As String Dim wsPivot As Worksheet Dim wsData As Worksheet Dim strDest As String strPivotName = "Pivot" Set wsPivot = ActiveWorkbook.Sheets.Add wsPivot.Name = strPivotName strDest = "'" & strPivotName & "'!R5C3" Set wsData = Sheets("Data") ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ wsData.Range("A1").CurrentRegion).CreatePivotTable _ TableDestination:=strDest, TableName:= _ "PivotResults", DefaultVersion:=xlPivotTableVersion10 With wsPivot .PivotTables("PivotResults").AddFields RowFields:="Code", _ ColumnFields:="Week" With .PivotTables("PivotResults").PivotFields("Q") .Orientation = xlDataField .Caption = "Sum of Q" .Function = xlSum End With End With End Sub '================================= David Howdon wrote: What I am trying to do is to use VBA to create a pivot table and place it in a particular named sheet. Playing with recording pivot table creation and tweaking that slightly I have got to Dim strPivotName As String strPivotName = "Pivot" ActiveWorkbook.Sheets.Add().Name = strPivotName ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Range("a1").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _ "PivotResults", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=Sheets(strPivotName).Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotResults").AddFields RowFields:="Code", _ ColumnFields:="Week" With ActiveSheet.PivotTables("PivotResults").PivotField s("Q") .Orientation = xlDataField .Caption = "Sum of Q" .Function = xlSum End With Apart from being incredibly ugly code this nearly does what I want. It creates the pivot table and puts it in the appropriately named sheet. However it also create a spurious blank sheet. I suspect this is something to do with the TableDestination:="" but replacing that with TableDestination:=Sheets(strPivotName causes an error. What am I doing wrong? -- Debra Dalgleish Contextures www.contextures.com/tiptech.html Blog: http://blog.contextures.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA for pivot tables
Debra Dalgleish wrote:
You can create a string with the sheet name and destination cell, e.g.: [snip] Thanks this worked. -- Each day a man watched a donkey walk past a high wood fence with one plank removed. Each day he saw a nose, then the ears, then the neck, forequarters, back and finally the tail. He pondered this for a time and eventually declared. “I understand now. The nose causes the tail” |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Any way to programmatically make pivot tables behave more like data tables? | Excel Programming | |||
(Tom?) Pivot tables, code to refer to all pivot tables on template | Excel Programming | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |