VBA automate the source data for a pivot table

[bgrice]

Hi. My code to create a pivot table was captured using the marco recorder. This specifies the source data as "CDRACBS!R2C1:R2804C9". However, this no longer works as it should because the source data continues to increase in size - specifically more rows are added to the source data. How do I write the code for this? Is it just a matter of always finding the last used row in the source data? The relevant part of the code is below. Thank you.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "CDRACBS!R2C1:R2804C9", Version:=6).CreatePivotTable TableDestination:= _
        "Suppliers!R3C1", TableName:="PivotTable1", DefaultVersion:=6

 

[Jimmypop]

Hi There

Have you tried turning your source data into a Excel Table and then replace "CDRACBS!R2C1:R2804C9" with the name of your table?