How do I copy and transpose from one worksheet to the first available row in another worksheet?

[PikaStu]

Hi, relatively new to working with macros and this one has stumped me. I’ve looked around various places and seemingly found other people wanting the same thing, but their solutions don’t seem to work for me.

Background: I have created a workbook which will act as a catalogue. Sheet 1 is a data entry page. Sheet 2 is the actual catalogue.
There are only a few fields required for each entry, but as it will all require manual entry, and there will eventually be hundreds of rows, I am trying to avoid manually scrolling to the next available row.

What I am trying to do: Copy/transpose sheet 1 range D4:D8 to sheet 2 in the first available row, when a button is clicked. The source data will always be from the same range, but the destination will be columns A:E.

Any help would really be appreciated, I have been trying to work this out for weeks!

 

[JLGWhiz]

You can put this in a standard code module1 and then assign it to a Form Controls button.

Sub copyRng()
Range("D4:D8").Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

 

[PikaStu]

You can put this in a standard code module1 and then assign it to a Form Controls button.

Sub copyRng()
Range("D4:D8").Copy
Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValues, Transpose:=True
Application.CutCopyMode = False
End Sub

Amazing thank you! It's very similar to a few things I have tried before but I couldn't tell you the difference between them!
I had to add in "Paste:=" before xlPasteValues to make it work, but I am familiar with PasteSpecial so that wasn't an issue. It was the finding a new row I just couldn't get my head around! It works perfectly (and its one of the most straightforward options I've tried).

 

[JLGWhiz]

Don't know why it was necessary to add the 'Paste:=' since 'xlPasteValues' falls in the first order of the parameters, but if it works for you, then so be it.
Regards, JLG