+ Reply to Thread
Results 1 to 5 of 5

changing range selection for pivot table based on worksheet

  1. #1
    Registered User
    Join Date
    03-31-2010
    Location
    Utah, America
    MS-Off Ver
    Excel 2007
    Posts
    18

    changing range selection for pivot table based on worksheet

    I've got some code that i need to go through every different worksheet in my workbook and create a pivot table. I recorded the macro to make the pivot table, but i don't know how to change the source data to be flexible. basically i need it to always be from row 2 column 1 to row (last row) column 8, so only the second row will change. also, I don't know how to change the worksheet source, so that it is always the active worksheet.

    here is what i have so far:

    Please Login or Register  to view this content.
    my main issues are coming at the lines:

    Please Login or Register  to view this content.
    and you can see that the sheet for source data is set, as well as the sheet for the pivot table (which i also don't know how to change to the active sheet)

    any thing will help, thanks

  2. #2
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: changing range selection for pivot table based on worksheet

    Hi,

    A simple solution for you is to name your ranges and use Dynamic ranges ... i.e ranges which adapt themeselves to the database ...

    Should you need some explanation ...
    http://www.contextures.com/xlNames01.html#Dynamic

    HTH

  3. #3
    Registered User
    Join Date
    03-31-2010
    Location
    Utah, America
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: changing range selection for pivot table based on worksheet

    that sounds good, my only problem, is that the names and number of worksheets that I will be cycling through will vary from time to time, is there a way to make the dynamic range one that will automatically change for different worksheets rather than defining one range for each of the sheets?

  4. #4
    Valued Forum Contributor JeanRage's Avatar
    Join Date
    03-02-2009
    Location
    Nice, France
    MS-Off Ver
    Excel 2003
    Posts
    705

    Re: changing range selection for pivot table based on worksheet

    Hi,

    If you have a great deal of worksheets and database ranges, a macro can automatically generate all the Range Names ...

    HTH

  5. #5
    Registered User
    Join Date
    03-31-2010
    Location
    Utah, America
    MS-Off Ver
    Excel 2007
    Posts
    18

    Re: changing range selection for pivot table based on worksheet

    It ended up being even easier than that, i just defined a variable as the last row, and set the source data equal to
    Please Login or Register  to view this content.
    and the lastRow variable changes with each worksheet

    Thanks for your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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