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

I did.  I used Rick's.  It had the fewest lines of code and was not using th paste function.  Thanks for your help

Answer 3

Does that mean you are not to say what you used or mark an answer.....................????????????????

---

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

Answer 4

Thanks everyone for your help!!!!

Answer 5

And, just in case you NOW say you want to copy a row to a column. In the future PLEASE try to state the need the FIRST time you post

Sub copynamedrangeTRANSPOSE_SAS()

Range("NamedRangeMultiCells").Copy

Sheets("sheet2").Range("f2").PasteSpecial Paste:=xlPasteValues, _

Operation:=xlNone, SkipBlanks:=False, Transpose:=True

End Sub

---

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