Try

wsDest.Range("A" & lngRow).Resize(1, rngTemp.Rows.Count) = _
WorksheetFunction.Transpose(rngTemp.Value)

If this post helps click Yes
---------------
Jacob Skaria


"Neil Holden" wrote:

They must be a simple way to do this, thanks for your attempt though much
appreciated. I am struggling with this and unfortunately your solution
didn't work.

Back to the drawing board.

Thanks again AB

"AB" wrote:

I don't even know if you can do that without simply looping through
your temprange and then putting each value into your target range
according cell.

What you can certainly do (instead of the looping i mention above) is
actually copy/paste[transpose] it - something along these lines:

rngTemp.Copy
wsDest.Range("A" & lngRow).PasteSpecial
xlPasteValues,xlPasteSpecialOperationNone,false,tr ue



On Nov 25, 9:56 am, Neil Holden
wrote:
Morning all, i have a range of cells that i have copying and pasting into
another workbook, when pasting it is pasting it vertically for example 1a,
a2, a3 etc i need it to paste horizontally as in transpose, can you help me
and tell me which bit of code i need to change?

Thanks.

Sub Button1_Click()

Dim Response As String
Dim DefaultFolder As String, DefaultFileName As String
Dim FileToSave
Dim OutApp As Object 'this emails operations manager
Dim OutMail As Object
Dim strbody As String

Response = MsgBox("Are you sure you want to Submit this to Procurement?", _
vbYesNo + vbInformation + vbDefaultButton2)

If Response = vbYes Then

Dim lngRow As Long, rngTemp As Range
Dim wbBook As Workbook, wsDest As Worksheet

Set rngTemp = ActiveSheet.Range("b9:b32")
Set wbBook = Workbooks.Open("\\sguk-app1\business
Objects\SubContract\Data.xls")
Set wsDest = wbBook.Sheets("Sheet1") 'Destination sheet

With rngTemp

lngRow = wsDest.Cells(Rows.Count, "A").End(xlUp).Row + 1

wsDest.Range("A" & lngRow).Resize(rngTemp.Rows.Count, _
rngTemp.Columns.Count) = rngTemp.Value

End With

.