none
Adjusting Pivot Table Data Source. RRS feed

  • Question

  • 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….

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

    Tuesday, March 1, 2016 4:01 PM

All replies

  • any help will greatly be appreciated.

    thanks!!

    Tuesday, March 1, 2016 8:32 PM
  • What I have bolded below is the source data shown in row-column format.

      ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _

            "DATA!R1C1:R534C15", Version:=xlPivotTableVersion14).CreatePivotTable _

            TableDestination:="Summary!R3C1", TableName:="PivotTable1", DefaultVersion _

            :=xlPivotTableVersion14

    You need to adjust R1C1:R534C15  to the range you want.  For example, if your data was in A1:C3 you woudl update the code to read  R1C1:R3C3.

    Good luck.

    Wednesday, March 2, 2016 2:21 AM