basic code for copy transpose paste to next row

Question

I'm completing a tracker. user will complete questions one one sheet - each answer will be in a cells (B2-B8) and copied to a row in another sheet. I want to paste to the next available line.

I have tried code that copies the range (B2-B8) and then paste transpose=True. This was an error. The next I tried coping each cell and pasting each cell. But not sure how to add in the paste to next row. I can't remember the coding to use to create a loop with set range or the code to add to next available row.

Range("B2").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A5").Select
ActiveSheet.Paste
Sheets("Settlement Request").Select
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("B5").Select
ActiveSheet.Paste
Sheets("Settlement Request").Select
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C5").Select
ActiveSheet.Paste

I have not gotten that far. When I tried the transpose = true I got a compile error.

chillin chillin · Accepted Answer · 2019-07-07T17:18:48

One approach might be:

Option Explicit

Sub TransferAnswersToSomeSheet()

    ' Copying a vertical range (on "Settlement Request" sheet)
    ' and pasting to a horizontal range (on "Sheet2" sheet).

    Dim sourceSheet As Worksheet
    Set sourceSheet = Worksheets("Settlement Request") ' If there is a workbook, specify it too e.g. ThisWorkbook or Workbooks("someWorkbookName")

    Dim destinationSheet As Worksheet
    Set destinationSheet = Worksheets("Sheet2") ' If there is a workbook, specify it too e.g. ThisWorkbook or Workbooks("someWorkbookName")

    Dim cellToPasteTo As Range
    Set cellToPasteTo = destinationSheet.Cells(destinationSheet.Rows.Count, "A").End(xlUp).Offset(1, 0)

    sourceSheet.Range("B2:B8").Copy
    cellToPasteTo.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Transpose:=True

    Application.CutCopyMode = False
End Sub

The code above will paste as values (whilst preserving original number formatting). If this is not appropriate for you, you can replace xlPasteValuesAndNumberFormats in the code with the options shown here: https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype.
The logic (in the code) is to find the last value in column A and paste to the row immediately below. The last value is found using Range.End(xlUp) and we access the cell immediately below it using Range.Offset(1,0).

basic code for copy transpose paste to next row

1 Answers