Changing the data source of pivot table in VBA

0
votes

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

1
excelvbapivot-table
Have you tried doing it manually and record it with the macro recorder? This is how I do this constantly as I can never remember the code for that. The code, the macro recorder is generating can be used almost without any changes (actually one of the few examples where the macro recorder performs extremely well including with). – Ralph
Yep. I tried recording it. But I got a hard time in replacing the cell reference. bc in my case I think I have to use the lastrow since it should traverse until the last used cell. – kruk22
So, do you mind sharing with is the VBA code you have thus far (which - apparently - you are merely trying to finalize with said lastRow)? – Ralph
@kruk22 try reading the answer in the post's link stackoverflow.com/questions/38919157/… , if still need help let me know – Shai Rado
Thanks for the link @Shai Rado :) It helps me! – kruk22

1 Answers

1
votes

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)