Transpose VBA troubles

[Glasgowsmile]

I've got the following formula and I need to add Transpose:=True to it but everything I've tried produces errors. Is there a simple way to do it for this?

Originally I was using .copy with .pastespecial xlpastevalues, transpose=:true but I wanted to see if the below approach would be faster for execution.

Sheets("Data").Range("Z10, Z14, Z18, Z20") = Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).Value

 

[gordsky]

what is the end result you are trying to achieve

 

[Glasgowsmile]

what is the end result you are trying to achieve

I just need data copied, which is in a vertical format to be transposed to horizontal. Wanted to see if it was possible with the existing code.

 

[gordsky]

Maybe something like

Sheets("Data").Range("Z10, Z14, Z18, Z20") = Application.WorksheetFunction.Transpose(Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).Value)

 

[Glasgowsmile]

Maybe something like

Sheets("Data").Range("Z10, Z14, Z18, Z20") = Application.WorksheetFunction.Transpose(Sheet1.Range("C" & Rows.Count).End(xlUp).Offset(1).Value)

Tried this but the range Sheet1("C") ends up being blank each time so it's not working

 

[gordsky]

Tried this but the range Sheet1("C") ends up being blank each time so it's not working

are you able to attach a sample sheet,