+ Reply to Thread
Results 1 to 4 of 4

Trying to get a Pivot Table to work with variable source data

  1. #1
    Registered User
    Join Date
    11-13-2005
    Posts
    21

    Trying to get a Pivot Table to work with variable source data

    I am trying use VBA to create a pivot table. The data is on a worksheet "ALL OUTSTANDING REMITS". I am sending the pivot table to a created worksheet "PreSummary". The data will always have the same number of columns, but the number of rows will vary.

    I recorded a macro to get the core code, however I would like to use a variable range for the source data. I have found multiple options that I think should work, however nothing is working for me. Here is the code from the macro that works:
    Please Login or Register  to view this content.
    My most recent attempt is this:

    Please Login or Register  to view this content.
    I have checked and the code is highlighting the correct range and the name exists in the name manager with the correct cells. When I run the macro, it errors on that last section with:

    "Run Time Error '1004' - This command requires at least two rows of source data. You cannot use the command on a selection in only one row..."

    So I am either passing the name to the argument incorrectly, or setting the range incorrectly, or something. I know that is probably not the most elegant creation of the named range, and I am open to suggestions. I also tried this:

    Please Login or Register  to view this content.
    This also selected the correct range, but gave the same error.

    Thanks for any help.
    Last edited by nakedbamboo; 03-02-2011 at 10:01 AM.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,180

    Re: Trying to get a Pivot Table to work with variable source data

    Try

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    11-13-2005
    Posts
    21

    Re: Trying to get a Pivot Table to work with variable source data

    Awesome, that worked great.Thank you very much.

  4. #4
    Registered User
    Join Date
    08-17-2012
    Location
    Philippines
    MS-Off Ver
    Excel 2007
    Posts
    1

    Smile Re: Trying to get a Pivot Table to work with variable source data

    Hi Andy, I also tried your idea and it worked. Thank you!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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