Paste special transpose

[montecarlo2079]

I need to copy a range in column B and paste it into another sheet transposed across column A

The problem is I need it to paste it into the first available open cell in the other sheet in column A.

The user can clear the data in the main sheet, but I need to be able to keep adding to the other sheet. In the end I Will be creating a file once the user is done.

so if the next blank cell is A7, I need the code to copy the data and paste special/transpose into A7.

How can I mock up this code to be able to copy a range from one sheet, and paste it into the next sheet with paste special transpose into the first available cell in column A?

Sub copy()
Worksheets("Info").Range("b1:b785").copy
Worksheets("File").Range("a1").PasteSpecial Paste:=xlValues, Transpose:=True

End Sub

 

[Jonmo1]

Try

Worksheets("File").Range("A" & Rows.Count).End(xlup).Offset(1,0).PasteSpecial Paste:=xlValues, Transpose:=True

 

[s hal]

I believe this should work:

Sub copy()
Sheets("Info").Range("b1:b785").copy
Sheets("File").Range("a1").End(xlDown).Offset(1).PasteSpecial Paste:=xlValues, Transpose:=True

End Sub

And I see I walked away far too long

 

[montecarlo2079]

Works perfectly!

thank you

 

[Jonmo1]

Glad to help, thanksf or the feedback.