Change Data Source in VBA code


  • I have a Pivot Table in Excel 2010.  I add data to Sheet1 everyday.  I use "Change Data Source" a lot.  I was wondering if there is VBA code for the source range that can be added to a button or Workbook_Open.

    Make a range:

    max = Sheets("Sheet1").Cells(Rows.Count,"C").End(xlUp).Row
    rng=Sheets("Sheet1").Range("A1:M" & max)


    I just do not know the VBA code for entering the range into the PivotTable Tools>Options>Change Data Source.


    I got as far as this:


    Max = Sheets("2011").Cells(Rows.Count, "F").End(xlUp).Row '63000
        ActiveSheet.PivotTables("PivotTable1").ChangePivotCache ActiveWorkbook. _
            PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
            "C:\Users\Philosophaie\Documents\[a Chase - Pivot Tables.xlsm]2011!R4C2:R10000C8" _
            , Version:=xlPivotTableVersion14)

    I need R10000 to be variable with Max.


    Tuesday, March 08, 2011 7:57 PM

All replies

  • You can set the data source of the pivot table to a dynamic range interactively. You only have to do it once and no code is required. See
    Regards, Hans Vogelaar
    Tuesday, March 08, 2011 8:40 PM
  • Here's a VBA solution:

        ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        Sheets("SourceDataForPivotTable").Range("A1").CurrentRegion).CreatePivotTable _
        TableDestination:=Sheets("DestinationSheetForPivotTable").Range("A3"), TableName:="PivotTable1", _

    This is what makes it dynamic:


    Tuesday, March 08, 2011 11:20 PM
  • I am looking to edit the current Pivot table.

    Max = Sheets("2011").cells(Rows.Count,"F").End(xlUp).Row

    The Dynamic:

    Sheets("2011").Range("B4:H" & Max).CurrentRegion

    I want to change the current region in sheet "2011" for the Pivot Table in Sheets("Pivot")

    Wednesday, March 09, 2011 7:11 AM