Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax in VBA to create a pivot table
Hi
An easy question: With this code to create a pivot table: Sub pt() Dim LastRow As Long Dim LastColumn As Long LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Sheets.Add.Name = "pivot" ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "cases-dump!R1C1:R3857C14", Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="pivot!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion12 Sheets("pivot").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Procedure Date") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("rvu"), "Sum of rvu", xlSum Range("A6").Select Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _ False, True, False, True) With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Years") .Orientation = xlColumnField .Position = 1 End With End Sub what is the correct syntax to replace: SourceData:= _ "cases-dump!R1C1:R3857C14" with SourceData:= _ "cases-dump!R1C1:R [LastRowcd ] C [Last Column]" I copied the code from the macro editor, and altered it a little, but need the correct R1C1 notation to use the last row and column in the (variable) source data. Thanks! Charles Snyder |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax in VBA to create a pivot table
try: SourceData:= _ "cases-dump!R1C1:R" & lastrow & "C" & lastcolumn, ... "Charles L. Snyder" wrote: Hi An easy question: With this code to create a pivot table: Sub pt() Dim LastRow As Long Dim LastColumn As Long LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Sheets.Add.Name = "pivot" ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "cases-dump!R1C1:R3857C14", Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="pivot!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion12 Sheets("pivot").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Procedure Date") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("rvu"), "Sum of rvu", xlSum Range("A6").Select Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _ False, True, False, True) With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Years") .Orientation = xlColumnField .Position = 1 End With End Sub what is the correct syntax to replace: SourceData:= _ "cases-dump!R1C1:R3857C14" with SourceData:= _ "cases-dump!R1C1:R [LastRowcd ] C [Last Column]" I copied the code from the macro editor, and altered it a little, but need the correct R1C1 notation to use the last row and column in the (variable) source data. Thanks! Charles Snyder -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
syntax in VBA to create a pivot table
Basically, it is like this: Range("A1").Select ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ Sheets("Sheet1").Range("A1").CurrentRegion).Create PivotTable _ TableDestination:=Sheets("Sheet2").Range("A1"), TableName:="PivotTable1", _ DefaultVersion:=xlPivotTableVersion10 If you have a Sheet named 'cases-dump', then I surmise the code would be this: Sheets("cases-dump").Range("A1").CurrentRegion). Also, it seems like you are using XL'07, I use XL'03, thus the xlPivotTableVersion10 Just watch out for that... Good luck, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Charles L. Snyder" wrote: Hi An easy question: With this code to create a pivot table: Sub pt() Dim LastRow As Long Dim LastColumn As Long LastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row LastColumn = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column Sheets.Add.Name = "pivot" ActiveWorkbook.PivotCaches.Create(SourceType:=xlDa tabase, SourceData:= _ "cases-dump!R1C1:R3857C14", Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="pivot!R3C1", TableName:="PivotTable1", DefaultVersion _ :=xlPivotTableVersion12 Sheets("pivot").Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Procedure Date") .Orientation = xlRowField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").AddDataFiel d ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("rvu"), "Sum of rvu", xlSum Range("A6").Select Selection.Group Start:=True, End:=True, Periods:=Array(False, False, False, _ False, True, False, True) With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Years") .Orientation = xlColumnField .Position = 1 End With End Sub what is the correct syntax to replace: SourceData:= _ "cases-dump!R1C1:R3857C14" with SourceData:= _ "cases-dump!R1C1:R [LastRowcd ] C [Last Column]" I copied the code from the macro editor, and altered it a little, but need the correct R1C1 notation to use the last row and column in the (variable) source data. Thanks! Charles Snyder |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to create pivot table from existing pivot table in excel 2007 | Excel Discussion (Misc queries) | |||
How do I create multiple pivot charts from one pivot table? | Charts and Charting in Excel | |||
Syntax problem defining range for pivot table | Excel Programming | |||
pivot table: create worksheetsheet using pivot table | Excel Discussion (Misc queries) | |||
How do I create a pivot table if the pivot table icon or menu ite. | Charts and Charting in Excel |