0

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

kruk22
  • 149
  • 2
  • 3
  • 15
  • 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 Sep 14 '16 at 08:14
  • 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 Sep 14 '16 at 08:19
  • 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 Sep 14 '16 at 08:22
  • 1
    @kruk22 try reading the answer in the post's link http://stackoverflow.com/questions/38919157/how-to-debug-a-pivot-chart-macro/38936385#38936385 , if still need help let me know – Shai Rado Sep 14 '16 at 08:25
  • Thanks for the link @Shai Rado :) It helps me! – kruk22 Sep 14 '16 at 08:56
  • @kruk22 (feel free to upvote the answer there :) – Shai Rado Sep 14 '16 at 09:00

1 Answers1

1

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)

a-burge
  • 1,535
  • 1
  • 13
  • 25