Results 1 to 2 of 2

Thread: Replace Pivot Table Source Range with New Address VBA

  1. #1
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10

    Replace Pivot Table Source Range with New Address VBA

    Hi All,

    Here is sub routine which enable you to replace the existing source range with new address in VBA.

    Code:
    Sub ReplacePivotSourceData(ByVal WorkbookName As String, ByVal PivotSheetName As String, _
                                ByVal PivotTableName As String, ByVal SourceDataSheetName As String, _
                                                                ByVal NewPivotSourceRange As String)
        
        '// Author      : Kris @ ExcelFox
        
        Dim wbkActive   As Workbook
        Dim ptPivot     As PivotTable
        Dim ptCache     As PivotCache
        Dim wksPivot    As Worksheet
        
        Set wbkActive = Workbooks(CStr(WorkbookName))
        Set wksPivot = wbkActive.Worksheets(CStr(PivotSheetName))
        
        Set ptPivot = wksPivot.PivotTables(CStr(PivotTableName))
        
        Set ptCache = wbkActive.PivotCaches.Create(1, wbkActive.Worksheets(CStr(SourceDataSheetName)).Range(CStr(NewPivotSourceRange)).Address(external:=1))
        
        ptPivot.ChangePivotCache ptCache
        ptPivot.PivotCache.Refresh
        
        Set wbkActive = Nothing
        Set ptPivot = Nothing
        Set ptCache = Nothing
        Set wksPivot = Nothing
        
    End Sub
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  2. #2
    Junior Member
    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0
    Thank you. After trying dozens of various solutions found online, only this one works perfectly.

Similar Threads

  1. Replies: 3
    Last Post: 03-05-2013, 03:57 PM
  2. Create a Pivot table
    By NITIN SHETTY in forum Excel Help
    Replies: 3
    Last Post: 01-26-2013, 11:01 AM
  3. Lookup and Count Using Pivot Table
    By RobExcel in forum Excel Help
    Replies: 2
    Last Post: 12-21-2012, 11:08 AM
  4. Filter more than one pivot table at one time
    By larryt1940 in forum Excel Help
    Replies: 8
    Last Post: 05-04-2012, 06:45 PM
  5. Replies: 6
    Last Post: 04-03-2011, 09:46 PM

Tags for this Thread

Posting Permissions

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