Pivot Table Question

[cmiedaner]

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.

 

[benmcclave]

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 Datatab 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 indata).

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

 

[cmiedaner]

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 theData tab and then click the Table1 tab, this event will fire. If you don'thave 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.