paste values from named dynamic range to another worksheet

[Nate H]

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

 

[Tom Ogilvy]

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

 

[Nate H]

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