Copying a range of cells in a column in one sheet to specified cells in a row in another

Viewed 127

I've been doing a lot of surfing around for answers but I'm so green when it comes to writing VB code that I need specific help on how to write the code in my case. None of my copy&paste adventures have worked, so I'm hoping you'll be patient with me.

The current code I have is this:

    Sub CopyPaste()

Dim targetRng As Excel.Range
Dim destRng As Excel.Range
Set targetRng = Range("K6:K14,K19:K20")

With Excel.ThisWorkbook.Sheets("Database")
    Set destRng = .Cells(2, .Columns.Count).End(Excel.xlToLeft).Offset(0, 1).Resize(targetRng.Rows.Count, targetRng.Columns.Count)
    destRng.Value = targetRng.Value
End With

With Excel.ThisWorkbook.Sheets("Email")
Range("J6:J14,J19:J20").ClearContents
End With

End Sub

... which works well, except that I want to post the data in a row, rather than in a column. (NB It's correct that the target range to copy is column K and the range to clear is J - as column J has drop-down lists to select from)

So I've sussed out that the bit of code I need to add is:

.PasteSpecial Paste:=xlPasteValues, Transpose:=True

But I can't for the life of me figure out how or where to get that in there, and all the errors I'm getting are doing my head in.

Also, the data I am copying out is a range with a gap (K6:K14,K19:K20)- but the second range (K19:K20) doesn't paste into the 'Database' sheet. The .ClearContents function works fine for both sets in the range in column J, so I'm not seeing where I'm going wrong with that one either.

Take pity on the code noob?

1 Answers
Related