Results 1 to 3 of 3

Thread: Change data source for all pivot tables.

  1. #1
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    52
    Articles
    0
    Excel Version
    2019

    Change data source for all pivot tables.



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

    Hello VBA coders
    I have an excel workbook with 3 different data sources and 2 pivot tables. The data sources are in a1:c8, e1:f8 and h1:j8. I tried to write sub procedure that changes the data source to one of the 3 data sources. Everything is on the same worksheet. My sub procedure does not work. Does anyone know why?

    Code:
    Sub chdatasourse()
    
    
    Dim pt As PivotTable
    
    
    'Get the range from the user
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox(prompt:="Enter Range", Type:=8)
    If rng Is Nothing Then
    MsgBox ("Operation cancelled")
    Else
    rng.Select
    End If
    
    
    For Each pt In ActiveSheet.PivotTables
    
    ActiveSheet.PivotTables(pt).ChangePivotCache ActiveWorkbook. _
    PivotCaches.Create(SourceType:=xlDatabase, SourceData:=ActiveWorkbook.ActiveSheet.Range(rng), _
    Version:=xlPivotTableVersion14)
    
    Next
    
    End Sub

  2. #2
    Seeker gue's Avatar
    Join Date
    Nov 2018
    Posts
    19
    Articles
    0
    Excel Version
    Office Professional Plus 2016
    Best would be to provide a sample excel sheet.
    But two of your 3 data sources have 3 columns (A:C, H:J), and one only two(E:F). How to you want to handle this? You would have to rebuild your pivot table by VBA. Changing Cache is not sufficient.

  3. #3
    Acolyte Heyjoe's Avatar
    Join Date
    Jan 2019
    Location
    USA
    Posts
    52
    Articles
    0
    Excel Version
    2019
    Thanks Gue. I figured it out.

Posting Permissions

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