CODEWITHSUNDEEP
excelvbapivot-table
kruk22
kruk22

Reputation: 159

Changing the data source of pivot table in VBA

My problem goes like this: I have 2 worksheets in 1 workbook which are the "Source Data" and the "Pivot Sheet". Everytime I generate(I generate data every week and it's in macros), data are added to Source Data sheet but the Pivot Table in the Pivot Sheet remains the same. I think it's quite handy if I will still have to Change Data Source everytime I'll generate the report. I want it to automatically adjust it's data source until the last used cell. How to do it? Columns used as Source are from Column C to W Please help! :(

Upvotes: 0

Views: 1611

Answers (1)

a-burge
a-burge

Reputation: 1584

If you create tables (ctrl + t) for your source data and direct your pivot to read those tables, they should automatically expand as new data is entered. Then it should be as simple as Thisworkbook.Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh (based on the macro recorder)

Upvotes: 1

Related Questions