I saw a similar question in an earlier post and tried to use the solution but was not able to get it to work.

I have a pivot table in sheet Table1, workbook Monitor1. My data is in sheet "Data". over the course of the day, I add data and was hoping there was a macro that would expand the data range of the pivot table and refresh the table to reflect the new data.

Thanks in advance for your help.

Hi,

Assuming that the data feeding your PivotTable has been formatted as a Table called "Table1", you can paste this code to the module for the "Data" sheet (right-click the tab name and click "View Code"). This sub will only fire when you leave the data tab, so if you enter a bunch of data on the Data tab and then click the Table1 tab, this event will fire. If you don't have your data formatted as a table, then comment out that line and uncomment the line below to use currentregion (works best if no blank rows/columns in data).

Hope this helps,

Ben

Code:

Private Sub Worksheet_Deactivate()

With Sheets("Table1").PivotTables(1)
.SourceData = "Table1[#All]" 'Or uncomment next line if not formatted as a table
'.SourceData = "Data!" & Sheets("Data").Range("A1").CurrentRegion.Address(, , xlR1C1)
.PivotCache.Refresh
End With

End Sub

On Wednesday, September 12, 2012 4:51:25 PM UTC-4, (unknown) wrote:
Hi, Assuming that the data feeding your PivotTable has been formatted as a Table called "Table1", you can paste this code to the module for the "Data" sheet (right-click the tab name and click "View Code"). This sub will only fire when you leave the data tab, so if you enter a bunch of data on the Data tab and then click the Table1 tab, this event will fire. If you don't have your data formatted as a table, then comment out that line and uncomment the line below to use currentregion (works best if no blank rows/columns in data). Hope this helps, Ben Code: Private Sub Worksheet_Deactivate() With Sheets("Table1").PivotTables(1) .SourceData = "Table1[#All]" 'Or uncomment next line if not formatted as a table '.SourceData = "Data!" & Sheets("Data").Range("A1").CurrentRegion.Address(, , xlR1C1) .PivotCache.Refresh End With End Sub

Thank you very much. That workded well.