How do I change the data range in a pivot table? I used recorded macro to get this information. I need the Source Data row to automatically adjust the Source Data (row/columns)
Below is my code. text is in red….
Range("A1").Select
On Error Resume Next
mylastrow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
mylastcol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcol).Address
Data_range = "A1:" & mylastcell
Range(Data_range).Select
'Sheets("DATA").Select
Sheets.Add
ActiveSheet.Name = "Summary"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R534C15", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Summary").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PGM")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TYPE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BCOC")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("I_INVC_PAY_AMT"), "Sum of I_INVC_PAY_AMT", xlSum
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Thanks!!!
Below is my code. text is in red….
Range("A1").Select
On Error Resume Next
mylastrow = Cells.Find("*", [a1], , , xlByRows, xlPrevious).Row
mylastcol = Cells.Find("*", [a1], , , xlByColumns, xlPrevious).Column
mylastcell = Cells(mylastrow, mylastcol).Address
Data_range = "A1:" & mylastcell
Range(Data_range).Select
'Sheets("DATA").Select
Sheets.Add
ActiveSheet.Name = "Summary"
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R534C15", Version:=xlPivotTableVersion14).CreatePivotTable _
TableDestination:="Summary!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion14
Sheets("Summary").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("PGM")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("TYPE")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("BCOC")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("I_INVC_PAY_AMT"), "Sum of I_INVC_PAY_AMT", xlSum
With ActiveSheet.PivotTables("PivotTable1")
.InGridDropZones = True
.RowAxisLayout xlTabularRow
End With
Thanks!!!