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.