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

Should be as easy as inserting a comma in the resize to copy the ROW to the ROW

Sub copynamedrangeSAS()

Set myrng = Range("NamedRangeMultiCells")

Sheets("sheet2").Range("f2").Resize(, myrng.Count).Value = myrng.Value

End Sub

---

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

Answer 3

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.

Mike - I tried yours and it worked very quickly, but it is copying over the formuals and I need to copy the values.  Is there a way to do this?

Don - I tried yours and the one that does nto do values works, but I need values.  The one that was supposed to do values did not work.

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?

Answer 4

I wouldn't use Offset for that... I would use Cells instead of Range and set the offset from "p" in that....

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

Note that I chose to use the cell count of NamedRangeMultiCells (which I have assumed isalways going to be in a single column) instead of hard coding it like this....

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

just in case you want to change which cells it is comprised of in the future. If the number of columns could change also, then you should use this instead of my first line of code...

With Range("NamedRangeMultiCells")

    Worksheets("Sheet2").Cells(p + 3, "A").Resize(.Rows.Count, .Columns.Count) = .Value

End With

---

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 5

Should have been this (but you would have figured that out)

Sub copynamedrange()

Set myrng = Range("NamedRangeMultiCells")

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

End Sub

---

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