vba - Transpose without clipboard

[Joeybagadonuts]

I basically want to copy paste values and formats, tranposed. Without using the clipboard.

Is there a way using something like the Copy Destination:= or something like that.

Thanks for any help.

 

[Norie]

Can you give some more information?

Are you doing this in code?

 

[Joeybagadonuts]

Yeah, instead of something like this:

Range("I8:I91").Copy
Windows("temp.txt").Activate
Cells(age - 2, 1).PasteSpecial Paste:=xlValues, Transpose:=True
Cells(age - 2, 1).PasteSpecial Paste:=xlFormats, Transpose:=True: Application.CutCopyMode = False

I know there is a method of skipping the clipboard to copy - paste...like this:


Sheet1.Range("A1:A200").Copy Destination:=Sheet2.Range("B1")


But how to paste and transpose without the clipboard?

 

[Norie]

I'm sorry but I just don't think you can.

I suppose you could write code that copied and pasted each cell with the transpose. But that kind of defeats the point.

 

[Joeybagadonuts]

Yeah...I need greasy fast speed.
Thanks Norie.
Any other ideas anyone?

 

[Norie]

Joey

You could use the Transpose worksheet function.

Range("A1:E1") = WorksheetFunction.Transpose(Range("A3:A7"))

I'm not quite sure how you would implement that in your code and I'm not sure if it would have any impact on performance.

 

[Joe Was]

Sub myTrans()
Dim mySel As Range, myPst As Range
Dim myCR&, myCC&, myPR&, myPC&

Set mySel = Application.InputBox(prompt:="Select Copy Range:", Type:=8)
myCC = mySel.Columns.Count
myCR = mySel.Rows.Count

Set myPst = Application.InputBox(prompt:="Select Paste Range:", Type:=8)
myPC = myPst.Columns.Count
myPR = myPst.Rows.Count

If (myCC <> myPR Or myCR <> myPC) Then _
MsgBox "Your Paste Range must be the same size as your Copy Range size!": GoTo myEnd:

Range(myPst.Address) = WorksheetFunction.Transpose(Range(mySel.Address))

myEnd:
End Sub

 

[Joeybagadonuts]

Thanks guys

I think the WorksheetFunction might do the trick!