+ Reply to Thread
Results 1 to 3 of 3

Change Pivot Table Data Source based on Dynamic Range

  1. #1
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Change Pivot Table Data Source based on Dynamic Range

    I have a workbook which fires off a series of macros, but I just realized that my Pivot Tables don't update properly as the source worksheet/tab is constantly changing in terms of number of rows that need to be referenced.


    So basically, the source sheet named: "S_Detail" can vary in terms of rows (can go up or down), but will always have the same number of columns
    The sheet with the Pivot Tables, "PT_Count" contains 3 pivot tables that all need to reference the entirety of the "S_Detail" sheet.

    Rather than clicking on each of the 3 pivot tables in "PT_Count", going to Options > Change Data Source > and changing my range (with respect to number of rows in the "S_Detail" sheet), how would I do this through VBA to capture an ever-moving dynamic range in "S_Detail"?

    By the way, the 3 pivot tables in "PT_Count" are:
    PivotTable3
    PivotTable5
    PivotTable6

    I've tried a few different methods, but I'm coming up with errors.

    Any help would be greatly appreciated!
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Change Pivot Table Data Source based on Dynamic Range

    I took a quick look at your workbook and immediately saw something you can do to avoid dynamic named ranges and changes to the pivot table data sources.

    Change your source data, on the S_Detail sheet, to an Excel Table.
    ...Tables are one of the better features of Excel. They automatically expand and contract to accommodate the available data AND referencing them creates Structured Table References (which are descriptive, so you quickly know which data is being referenced)

    Select your source data on the S_Detail sheet...range A2 through the bottom right corner of your data
    Home.Format_as_table...follow the prompts (the default name will be Table1, in your workbook)
    Change the pivot table data sources
    ...Select a pivot table
    ...PivotTableTools...Options...Change_Data_Source
    ...Navigate to the source data and select all of it.
    ......Excel will use this reference: Table1[#All]
    ...Click: OK

    That's it!
    Now, whenever the dimensions of the Excel Table changes, all you have to do is refresh the pivot table(s). Since they reference the Table...all of the table data will be included.

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    05-24-2012
    Location
    Nashville, TN
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Change Pivot Table Data Source based on Dynamic Range

    Ron,

    That is a very novel solution!
    It does work as suggested, and I am certainly grateful for you posting that approach.

    However, I would be curious to know if there is still a different way of approaching this using a VBA based macro to account for an ever changing dynamic range on my source data sheet?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change Pivot Table Data Source to range A7: last record in F column
    By johnstevens in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-09-2014, 08:35 AM
  2. Macro to change pivot table range after importing new source data
    By kevinm3u in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-07-2014, 12:27 PM
  3. Replies: 4
    Last Post: 06-25-2012, 03:25 PM
  4. Update Pivot table using a dynamic range for source data
    By Carl Thompson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-02-2011, 08:20 AM
  5. [SOLVED] Change the range of a pivot table data source
    By Tony White in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1