Pull from One Sheet and Transpose to Another Sheet (VBA)

[Eric G]

How do I pull a row of data from one sheet and transpose it as column into another sheet?

For example, I can pull from one range and transpose it to another range in the same sheet:

[FONT=Calibri][SIZE=3][COLOR=#000000]Range(Cells(1, 1), Cells(5, 1)) =WorksheetFunction.Transpose(Range(Cells(1, 5), Cells(1, 10)))[/COLOR][/SIZE][/FONT]

I can even identify the sheet name in the coding as long as (1) the pulling and transposing is done in the same sheet and (2) this sheet is also my active sheet. This coding does not work if I am on a different sheet:

[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Source").Range(Cells(1, 1), Cells(5,1)) = WorksheetFunction.Transpose(Sheets("Source").Range(Cells(1, 5),Cells(1, 10)))[/COLOR][/SIZE][/FONT]

However, I am UNABLE to pull from one sheet and transpose it into a different sheet:

[FONT=Calibri][SIZE=3][COLOR=#000000]Sheets("Destination").Range(Cells(1, 1), Cells(5,1)) =WorksheetFunction.Transpose(Sheets("Source").Range(Cells(1, 5),Cells(1, 10)))[/COLOR][/SIZE][/FONT]

Instead, I receive the following error: "Run-time error '1004: Application-defined or object-defined error"

What do I need to do differently?

 

[Fluff]

You haven't fully qualified the ranges in any of those codes. Your second code should be

Sheets("Source").Range(Sheets("Source").Cells(1, 1), Sheets("Source").Cells(5, 1)) = WorksheetFunction.Transpose(Sheets("Source").Range(Sheets("Source").Cells(1, 5), Sheets("Source").Cells(1, 10)))

Or slightly simpler

With Sheets("Source")
   .Range(.Cells(1, 1), .Cells(5, 1)) = WorksheetFunction.Transpose(.Range(.Cells(1, 5), .Cells(1, 10)))
End With

note the . in front of the word Cells

 

[Eric G]

After using your solutions as templates, I was able rewrite the code to meet my need to transpose to a different sheet:

Sheets("Destination").Range(Sheets("Destination").Cells(1, 1), Sheets("Destination").Cells(5, 1)) = WorksheetFunction.Transpose(Sheets("Source").Range(Sheets("Source").Cells(1, 5), Sheets("Source").Cells(1, 10)))

With Sheets("Destination")
   .Range(.Cells(1, 1), .Cells(5, 1)) = WorksheetFunction.Transpose(Sheets("Source").Range(Sheets("Source").Cells(1, 5), Sheets("Source").Cells(1, 10)))
End With

Much appreciate the assist. Thanks, Fluff.

 

[Fluff]

Glad to help & thanks for the feedback

If you are just using Hard coded ranges as per your example you can simplify things even further like

With Sheets("Destination")
   .Range("A1:A5") = WorksheetFunction.Transpose(Sheets("Source").Range("A1:J1"))
End With

Although it seems odd that you are trying to transpose 10 cells into 5

 

[Eric G]

It was a typo, but it doesn't matter as those ranges were "throw away" ranges anyway to help simplify my question. In other words, I didn't want my actual ranges to distract from what I was looking to have solved here. The final coding will be looking something more like this:

With ws2
   .Range(.Cells(SRow, MC1), .Cells((LastCol - SCol + SRow), MC1)) = WorksheetFunction.Transpose(ws1.Range(ws1.Cells(MR1, SCol), ws1.Cells(MR1, LastCol)))
End With

It is still a work in progress, but your help allowed me to build something beyond what was otherwise a showstopper for me. Of course, all of those variables are Dim'd defined.

Again, thanks!