Updating pivot table data dating service for overweight

10 Jan

With this method, you’ll get partial automation, without having to add macros to the file.Any time you close then re-open the file, the pivot table will be refreshed.The problem is that my code does not pick up the new data that I add to the data source each week for each pivot table.I manually have to change the data source for each pivot table before running the macro (it then works successfully) This is the code so far Sub Refresh Pivottables() Dim wks As Worksheet, PT As Pivot Table For Each wks In Worksheets For Each PT In wks. Refresh Table Next PT Next wks End Sub Is there a way of extending the data ranges for each pivot table in my code before refreshing, so I don't need to do this manually?Hi Everyone, I have a number of pivot tables on different sheets in an excel workbook.

You can use Excel VBA to automatically update a pivot table when its worksheet is activated.

Create( _ Source Type:=xl Database, _ Source Data:=wo File. It is optional, if you don't specify it, excel assumes active cell.

Current Region, _ Version:=xl Pivot Table Version12) 'Probably the problem is that you don't specify destination argument.

What is the most efficient way to solve this problem? Hi, I would first make one correction in that your pivot table data source should select the entire column - then when you add new data to the bottom the refreshed pivot table will pick this new data up automatically.

If you keep adding new columns for each week or something similar then I definitely suggest restructuring your data so that you have a week or month number column that captures this and populate that rather than having to adjust the data source each time.