syntax in VBA to create a pivot table

  • Thread starter Thread starter Charles L. Snyder
  • Start date Start date
C

Charles L. Snyder

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:=xlDatabase,
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").AddDataField
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
 
try:

SourceData:= _
"cases-dump!R1C1:R" & lastrow & "C" & lastcolumn, ...

Charles L. Snyder said:
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:=xlDatabase,
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").AddDataField
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
Click to expand...
 
Basically, it is like this:
Range("A1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("A1").CurrentRegion).CreatePivotTable _
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---
 
Back
Top