VBA to refresh Pivot Table data range and data

T

tsejoe

New member
Joined
Jul 2, 2015
Messages
2
Reaction score
0
Points
0
I would like to set the VBA codes so that Excel will update the data range and refresh the pivot tables, everytime when the data is copied and paste into a worksheet. The data source worksheet is "CorpStoresSOH"; and the pivottable is in the "CorpStores SOH Summary". It is found that it has Run time 91 error. I would like to use only to change Pivot data source as the preferred option.
can anyone advise what should be the right coding?


Worksheets("CorpStoresSOH").Activate
Range("A3").Select
lastrow = Worksheets("CorpStoresSOH").UsedRange.Rows.Count
Set MB58range = Worksheets("CorpStoresSOH").Range("A3:L" & lastrow)

Worksheets("CorpStoresSOH Summary").PivotTables("pivottable_2").ChangePivotCache ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=MB58range, Version:=xlpivottableversion15)

Thank you so much~~

Best Regards,
joe
 
You must log in or register to reply here.
Back
Top