Adjusting Pivot Table Data Source

[CHELLY]

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!!!

 

[CHELLY]

any help will greatly be appreciated!!