VBA Code - Using named range w/ offset function

Question

I am not well versed in code, but I am trying to get VBA to take a named stream and insert it on another page using an offset function.  It works when my named range is only one cell, but I can't get it to work when my named range is a stream of 20 cells.  How can I do this?  Or, maybe there is an entirely differnt way to go about doing this.

Can anyone help?!

Answer 1

Thanks everyone for trying to help.  I mde a mistake before and meant for my range to be a row not column.  So my NamedRangeMultiCells should go from a3 to g3.

Rick - I tried yours and my result is just a column of zeros.  Is there somethign I can do to make it go into rows instead (since I gave you the wrong info) AND have my value come through? 

Yes, the wrong information you gave us definitely affected my code line. Try this one instead...

Worksheets("Sheet2").Cells(p + 3, "A").Resize(,7) = Range("NamedRangeMultiCells").Value

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.

Answer 2

Sub copynamedrange()

  Range("NamedRangeMultiCells").Copy Sheets("sheet2").Range("a1")

End Sub

or for just the values

Sub copynamedrange()

Set myrng = Range("NamedRangeMultiCells")

Sheets("sheet2").Range("c2").Resize(myrng.Count).Value = _

                         Range("namrng3").Value

End Sub

---

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***

Answer 3

Hi,

You should avoid using unqualified ranges when copying data, try this

Set sht = Sheets("Sheet2")

Range("NamedRangeMultiCells").Copy sht.Range("a4").Offset(p - 1, 0)

---

If this post answers your question, please mark it as the Answer.

Mike H

Answer 4

I have two sheets the named range is on the first sheet and the destination is on the second.  I am not inserting between rows, just taking the value and putting it in a column.  Here is a short example that I hpe helpes.

This works for a named range that is one cell

Sheet1 has a named range in cell a3 called NamedRangeOneCell

Sheet2 is where I want to put the named range, but copy and paste takes too long, so I just wanted to assign the range.  This works for one cell.

Range("a4").Offset(p-1,0) = Range("NamedRangeOneCell")

How can I make it work for this scenario

Sheet1 has a named range in cell a3 to a10 called NamedRangeMultiCells

Sheet2 is where I want to put the named range from cell a4 to a11, but need to have the same type of offset as above.

Thnks!

Answer 5

More details please...

1. Tell us the cells that make up your "named stream" (I'm interested if they are contiguous or not and if they are in a row or column)
2. When you say "insert", is that a whole row (or column) in between other filled rows (or columns), or something else?
3. Exactly where do you want to insert at?

If you can give us a before and after example for the source and destination sheets, that would be helpful (you don't have so show us all 20 cells... 3 will be enough... we just want to get an idea of what you have in mind).

---

NOTE: Please mark the message or messages (yes, you can mark more than one) that answer your question as the "Answer" so that others will know your question has been resolved.