CODEWITHSUNDEEP
vbaexcel
superstacker007
superstacker007

Reputation: 35

VBA Excel: How to assign range of cells to another range of cells?

New Excel VBA user here. I feel like I made a silly syntax mistake.

I am trying to copy some vertical cells in one worksheet, to horizontal cells in another worksheet.

Worksheets("Master").Range("D14:F14").Value = Worksheets("Temp").Range("B12:B14").Value

However, the above command seems to only paste the value of B12 to cells D14:F14. Is there a way to paste the cells of B12, B13, and B14 to D14:F14?

Upvotes: 2

Views: 2435

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Use Application.Transpose():

Worksheets("Master").Range("D14:F14").Value = Application.Transpose(Worksheets("Temp").Range("B12:B14").Value)

Or PasteSpecial:

Worksheets("Temp").Range("B12:B14").Copy
Worksheets("Master").Range("D14").PasteSpecial xlPasteAll, , , True

If you only want values then the first is best. When transposing with PasteSpecial one must use the xlPasteAll. It will throw an error if one tries to paste values only with transpose as true.

Upvotes: 4

Related Questions