Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable References
Hi, I have a pivot table macro on a dynamic range of data with varying number of rows and columns. My code below isnt quite working, pretty sure its to do with: SourceData:= "Original!cells(x,1),cells(1,y)") Many Thks Sub test() x = Range("A50000").End(xlUp).Row y = Range("IV1").End(xlToLeft).Column Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData: _ "Original!cells(x,1),cells(1,y)").CreatePivotT abl TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cat4" _ ColumnFields:="Account" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Amount").Orientatio = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True ActiveSheet.Name = "Check" End Su -- T De Villier ----------------------------------------------------------------------- T De Villiers's Profile: http://www.excelforum.com/member.php...fo&userid=2647 View this thread: http://www.excelforum.com/showthread.php?threadid=56428 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivottable References
Change "Original!cells(x,1),cells(1,y)").
to this "Original!" & cells(x,1).Address & "," cells(1,y).Address). HTH Die_Another_Day T De Villiers wrote: Hi, I have a pivot table macro on a dynamic range of data with varying number of rows and columns. My code below isnt quite working, pretty sure its to do with: SourceData:= "Original!cells(x,1),cells(1,y)") Many Thks Sub test() x = Range("A50000").End(xlUp).Row y = Range("IV1").End(xlToLeft).Column Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Original!cells(x,1),cells(1,y)").CreatePivotT able TableDestination:="", TableName:= _ "PivotTable1", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Cat4", _ ColumnFields:="Account" ActiveSheet.PivotTables("PivotTable1").PivotFields ("Amount").Orientation = _ xlDataField ActiveWorkbook.ShowPivotTableFieldList = True ActiveSheet.Name = "Check" End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=564282 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with converting a block of cells with Absolute and mixed references to relative references | Excel Worksheet Functions | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Pivottable references and values | Excel Programming | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming |