VBA paste data into the next row

Question

I do not use VBA much but need to complete this task. I cannot figure out how to paste data from entry form into the Log spreadsheet. Here what I have so far.

It works. The only problem is it overrides what is there in the Log.

Sub RangeCopy_Transpose()

  
    Dim shRead As Worksheet
    Dim lastrow As Long
    Set shRead = Sheets("Log")
  
    Range("B3, B5, B7, B9, B11, B13, B15, B17,B19,B21,B23,B25").Copy
    shRead.Range("A2").PasteSpecial Transpose:=True
    
    Range("B3:B26").ClearContents
    
End Sub

[Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Sep 22 '20 at 00:15
Please read [Under what circumstances may I add “urgent” or other similar phrases to my question, in order to obtain faster answers?](//meta.stackoverflow.com/q/326569) - the summary is that this is not an ideal way to address volunteers, and is probably counterproductive to obtaining answers. Please refrain from adding this to your questions. — halfer, Sep 22 '20 at 18:50

score 0 · Answer 1 · answered Sep 22 '20 at 02:09

You are almost near. You need to find last data entry cell on sheet Log Column A. Then paste data to next empty cell. Try below sub.

Sub RangeCopy_Transpose()
Dim shRead As Worksheet
Dim lastrow As Long

    Set shRead = Sheets("Log")
    
    'Detect last row with data.
    lastrow = shRead.Cells(shRead.Rows.Count, "A").End(xlUp).Row
    
    Range("B3, B5, B7, B9, B11, B13, B15, B17, B19, B21, B23, B25").Copy
    shRead.Range("A" & lastrow + 1).PasteSpecial Transpose:=True 'Lastrow+1 will paste next cell from last data cell.
    
    Range("B3:B26").ClearContents
    
End Sub

VBA paste data into the next row

1 Answers1