Page 1 of 4 123 ... LastLast
Results 1 to 10 of 40
Like Tree3Likes

VBA - change PivotTable source for every PivotTable within worksheet.

This is a discussion on VBA - change PivotTable source for every PivotTable within worksheet. within the Excel Questions forums, part of the Question Forums category; I have a rather complex report which is based on data it references from specifically set out and formatted 9 ...

  1. #1
    New Member
    Join Date
    Oct 2013
    Posts
    47

    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

    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

    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

    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

    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

    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

    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
    29,766

    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

    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

    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.

Page 1 of 4 123 ... LastLast

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