VBA Past Transpose without using select

[Skyhawk32806]

Hi,

I'm working on a macro where I have to paste a range of data using transpose. It there an easy way to paste the data without using select? I know how to do it if I don't have Transpose...

My current code:

Sheet2.Range("D2:CU2:).Copy
Sheet3.Range("F2").PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

There is a lot more going inbetween the 2 lines, I just wanted to make it easier to read.

Thanks for any ideas

 

[Jonmo1]

The code you posted will work fine, it doesn't select anything.
Except of coarse for your little typo...

Sheet2.Range("D2:CU2:).Copy
should be
Sheet2.Range("D2:CU2").Copy

 

[Skyhawk32806]

Oh I actually typed it wrong, my initial code had select in. I didn't realize my incorrect code would actually work.
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Thanks!

 

[P.Holko]

You may try sth like this as well:

Dim R As Range

Arr = Sheet2.Range("D2:CU2").Value
Arr = WorksheetFunction.Transpose(Arr)

Set R = Sheet3.Range("F2").Resize(UBound(Arr, 1) - LBound(Arr, 1) + 1, 1)
R = Arr

 

[Skyhawk32806]

You may try sth like this as well:

Dim R As Range
 
Arr = Sheet2.Range("D2:CU2").Value
Arr = WorksheetFunction.Transpose(Arr)
 
Set R = Sheet3.Range("F2").Resize(UBound(Arr, 1) - LBound(Arr, 1) + 1, 1)
R = Arr

Great, thanks for that idea also!