+ Reply to Thread
Results 1 to 3 of 3

paste values from named dynamic range to another worksheet

  1. #1
    Registered User
    Join Date
    01-25-2006
    Posts
    2

    paste values from named dynamic range to another worksheet

    Hello all,

    I have a workbook that, on sheet1, I can select options from a bunch of list dropdown boxes. This in turn changes the size and contents of several named ranges on another worksheet (say sheet6).
    What I need is a macros with command button on sheet1 that will take these dynamic ranges and paste "the values" to a Worksheet called "WhatevertheRangeNameis""Hold". Each range has its own Hold sheet, and the top two rows of each hold sheet have "headers" that I dont want to copy over.
    (there are 6 ranges, but if anyone could show me how to do this once, I could go from there)

    The trick is that I then want to be able to change the sheet1 options and run the macros again, copying the values of the dynamic ranges into the appropriate "hold" sheets, without copying over the info the macro pasted the first time. I want to be able to do this repeatedly, thus building an 'order' of sorts in these worksheets.
    One caveat; in some cases a dynamic range might be sized to 0 x whatever, that is, not needed for that "round of submission". The macros would need to just pass over these ones.

    Thanks for any help and have a great day,

    Nate

  2. #2
    Tom Ogilvy
    Guest

    Re: paste values from named dynamic range to another worksheet

    Dim rng as Range, rng2 as Range
    Dim sh as Worksheet
    Set rng = Nothing
    Set rng2 = Nothing
    On Error Resume Next
    set rng = Worksheets("Data").Range("Name1").RefersToRange
    On Error goto 0
    if not rng is nothing then
    set sh = Worksheets("Name1_Hold")
    set rng2 = sh.cells(rows.count,1).End(xlup)(2)
    rng.copy rng2
    end if

    --
    Regards,
    Tom Ogilvy

    "Nate H" <[email protected]> wrote in
    message news:[email protected]...
    >
    > Hello all,
    >
    > I have a workbook that, on sheet1, I can select options from a bunch of
    > list dropdown boxes. This in turn changes the size and contents of
    > several named ranges on another worksheet (say sheet6).
    > What I need is a macros with command button on sheet1 that will take
    > these dynamic ranges and paste "the values" to a Worksheet called
    > "WhatevertheRangeNameis""Hold". Each range has its own Hold sheet, and
    > the top two rows of each hold sheet have "headers" that I dont want to
    > copy over.
    > (there are 6 ranges, but if anyone could show me how to do this once, I
    > could go from there)
    >
    > The trick is that I then want to be able to change the sheet1 options
    > and run the macros again, copying the values of the dynamic ranges into
    > the appropriate "hold" sheets, without copying over the info the macro
    > pasted the first time. I want to be able to do this repeatedly, thus
    > building an 'order' of sorts in these worksheets.
    > One caveat; in some cases a dynamic range might be sized to 0 x
    > whatever, that is, not needed for that "round of submission". The
    > macros would need to just pass over these ones.
    >
    > Thanks for any help and have a great day,
    >
    > Nate
    >
    >
    > --
    > Nate H
    > ------------------------------------------------------------------------
    > Nate H's Profile:

    http://www.excelforum.com/member.php...o&userid=30836
    > View this thread: http://www.excelforum.com/showthread...hreadid=504969
    >




  3. #3
    Registered User
    Join Date
    01-25-2006
    Posts
    2

    Wink Thanks Tom!

    Thank you very very much! I cant tell you how much I appreciate it.

    I will try it out, probably take me a while to work out the details...


    Thanks again!

    Sincerely,
    Nate

+ 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