Pivot table update

[Veeru]

Hi, do we have any VBA code or any formula to update pivot table when I enter new data. what I want is lets say currently data is up to May and my pivot table is also selected Month of may , now if I add June month data ....pivot table should refresh and date in pivot table should replace may to june and now my pivot table will be showing june month picture only.....Please suggest

[mancubus]

upload your workbook with source data and pivot table

[Veeru]

Please find attached source file and pivot...currently it is set as month "3"...what I need is if my new data belongs to month"4"...after pasting new data...pivot table should refresh automatically and month 3 will replace it with month 4. Thanks

[mancubus]

just copy/input new data into data sheet and hit the button on it.

when you hit the button on data sheet, it runs below code. the code updates the source data of the PivotTable1 on pivot sheet to include newly added data and changes the filter to the max value in column B of data sheet.

see the attached file.



Sub vbax_60020_PT_update()

Dim NewSource As String
Dim FilterMonth

With Worksheets("data")
NewSource = .Name & "!" & .Cells(1).CurrentRegion.Address(ReferenceStyle:=xlR1C1)
FilterMonth = Application.Max(.Columns(2))
End With

With Worksheets("pivot").PivotTables("PivotTable1")
.ChangePivotCache ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=NewSource)
.RefreshTable
.PivotFields("Month").CurrentPage = FilterMonth
End With

End Sub

[Veeru]

Yes that is working...great...thanks a ton... :-)

[mancubus]

you are welcome.

pls mark the thread as solved for future references.

[Veeru]

I am sorry but not sure how to make it solved.....

[mancubus]

see my signature which reads:

Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)

[Veeru]

Done and thank you