Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 40

Thread: VBA - change PivotTable source for every PivotTable within worksheet.

  1. #1
    New Member
    Join Date
    Oct 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA - change PivotTable source for every PivotTable within worksheet.

    I have a rather complex report which is based on data it references from specifically set out and formatted 9 PivotTables located on a seperate worksheet. The PivotTables pull data from an Excel spreadsheet (specific range) which is located on a network drive. At the moment whenever I want to run the report with new version of data I'm manually updating data sources of all PivotTables to refresh the report by selecing PivotTable and going Change Data Source and selecting a range from external worksheet and repeating this action for all PivotTables.

    The connection string I use in the Change Data Source looks like this:
    "K:\FolderA\FolderB\FolderC\2013-14\\[nameOfTheFile.xlsx]Data 13-14'!$A$1:$GM$130253"

    This is a pseudocode of what I would like to achieve:

    Code:
    For each PivotTable in Worksheet
                    PivotTable.DataSource = "new source"
                    PivotTable.Refresh
             Next PivotTable
    Last edited by d'Holbach; Oct 12th, 2013 at 08:13 AM.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    Welcome to MrExcel.

    Do your PivotTables share the same PivotCache?
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Oct 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    Thank you for your reply.

    I don't know, how can I find out? All PivotTables should be linked to the same data source (spreadsheet).

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    What does:

    MsgBox ThisWorkbook.PivotCaches.Count

    tell you?
    Microsoft MVP - Excel

  5. #5
    New Member
    Join Date
    Oct 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    It looks like they all are linked to the same one, msg box returns "1".

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    Then you should be able to change the PivotCache's Connection property.
    Microsoft MVP - Excel

  7. #7
    New Member
    Join Date
    Oct 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    For some reason I'm not able to read the connection string, if I go with:

    MsgBox ActiveWorkbook.PivotCaches.Item(1).Connection

    I get error 1004?

  8. #8
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    30,353
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    If you're just using a range as the source, you should be looking at the Sourcedata property rather than Connection.

  9. #9
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    This works for me:

    Code:
    MsgBox ActiveWorkbook.PivotCaches(1).Connection
    If you select the pivot table can you choose Connection Properties if you click the dropdown on Change Source Data in the Data group on the Ribbon's Pivot Table Tools Options tab?
    Microsoft MVP - Excel

  10. #10
    New Member
    Join Date
    Oct 2013
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA - change PivotTable source for every PivotTable within worksheet.

    Quote Originally Posted by RoryA View Post
    If you're just using a range as the source, you should be looking at the Sourcedata property rather than Connection.
    Thank you.

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com