+ Reply to Thread
Results 1 to 9 of 9

Macro to change pivot table range after importing new source data

  1. #1
    Registered User
    Join Date
    01-24-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    10

    Macro to change pivot table range after importing new source data

    So I have been working on a project that utilizes a pivtot table with monthly data in it to do and display all sorts of other things... Those macros, for the most part, function ok.

    I am at the stage where I'm now trying to set up a system so that come the beginning of the new month, the updated data can be imported from the new spreadsheet painlessly.

    I have a macro that clears the old data and pastes the new stuff in its place. Now a problem here is that the number of rows will change month to month (columns will be 100% identical) and I need to adjust the pivot table source range accordingly.

    I can count up to the last row of data and get an integer telling me what row is the last one, but I don't know how to pass that to change the data range of the pivot table.

    Any ideas on a method?

  2. #2
    Registered User
    Join Date
    01-24-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Macro to change pivot table range after importing new source data

    Here is a simplified version of my first attempt in which i get a runtime error 1004 : method range of object_worksheet failed

    Please Login or Register  to view this content.
    The same code works with the "targetsheet.Range(Cells(1, 1), Cells(lastrow, 259)) _" line replaced with

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-24-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Macro to change pivot table range after importing new source data

    anyone have a thought? I'm still plugging away but not getting anywhere on this particular part.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Macro to change pivot table range after importing new source data

    If you will use a Table, you will not require any code at all - only to refresh the pivot table.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  5. #5
    Registered User
    Join Date
    01-24-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Macro to change pivot table range after importing new source data

    Quote Originally Posted by Izandol View Post
    If you will use a Table, you will not require any code at all - only to refresh the pivot table.
    I do not believe that refreshing the table will adjust it's source range. For instance if i change the source data from a 2000 row table to a 2100 row table, the pivot will not see the new 100 rows of data unless I tell it to change it's source range. Inversely, if it goes from 2000 rows to 1800 rows I will end up with a bunch of blank lines in my data that I dont want there.

  6. #6
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Macro to change pivot table range after importing new source data

    That is incorrect. If you use a Table, and set the name of the table as the source data for the pivot table, the Table will expand as you add data to it.

  7. #7
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Macro to change pivot table range after importing new source data

    If you will use code you may try:
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-24-2014
    Location
    Michigan
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Macro to change pivot table range after importing new source data

    That seems to work! Thanks! Anyway you could verbally walk me through what this code is doing? I'm really trying to learn the code I'm using so I can use it again in the future (so I don't have to keep asking questions I'm sure have been asked a million times!)

    Please Login or Register  to view this content.

  9. #9
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Macro to change pivot table range after importing new source data

    Of course. First part:
    Please Login or Register  to view this content.
    is reference to table starting at cell A1. (same as selecting A1 then press Ctrl+A)
    Then code returns the address for this table in R1C1 format - I am told that pivot tables are better with this - and prefixes this with sheet name. So SourceData argument will be like:
    Please Login or Register  to view this content.
    if table is A1:D1000 for example.

+ 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. Macro to Change Data Source on Pivot Table
    By Jiptastic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2013, 03:40 PM
  2. [SOLVED] Assigning named Range as Pivot Table Source Data using Macro
    By Melovalicious in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-26-2012, 05:50 AM
  3. Pivot Table : Can't change source range
    By RED Rose in forum Excel General
    Replies: 0
    Last Post: 08-30-2010, 03:08 PM
  4. Change data source of pivot table using macro
    By ashishprem in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2008, 01:11 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