transpose Copyfromrowset

[Newman Emanouel]

Dear All

I have an ado connection copying data from sql server and all works fine but
I need the data to be written to excel transposed.

What I mean is that copyfromrowset copies the informaion

DataA DataB DataC

I need to tranpose this information to write like

DataA
DataB
DataC

Below is the line of code in question

Sheets("Provision").Range("A35").CopyFromRecordset rsPubs


Can anyone help please

Regards

Newman

 

[Tom Ogilvy]

Sub ABC()
Dim r As Range, r1 As Range
With Sheets("Provision")
.Range("A35").CopyFromRecordset rsPubs
Set r = .Range("A35").CurrentRegion
r.Copy
Set r1 = r(r.Rows.Count + 5, 1) _
.Resize(r.Columns.Count, r.Rows.Count)
r1.PasteSpecial Paste:=xlPasteAll, Transpose:=True
r.ClearContents
r1.Copy r
r1.ClearContents
' then some code to clean up any debris.
End With

End Sub

 

[Newman Emanouel]

Tom

Thank you I understand the logic now I thought it would have been more
complicated.

Regards