Results 1 to 4 of 4

Thread: VBA to refresh Pivot Table data range and data

  1. #1

    VBA to refresh Pivot Table data range and data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365

  3. #3
    Any advice please?

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,512
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by tsejoe View Post
    Any advice please?
    Unwilling to do so since you haven't read/understood/complied with forum rules, either here or on the other site.
    tsejoe, please have a read of http://www.excelguru.ca/content.php?184 and take the necessary actions.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •