Copy and Paste Row from Sheet(1) to First Empty Row in Sheet(2)

Hi -

I am looking for a macro which will copy data from B6:R6 from Sheet1, and paste this data to the first available (empty) row in Sheet2, same columns: B:R. Thus, every time I enter new data on Sheet1 and run the macro, the new data will be added to the first empty row in Sheet2.

Any help would be greatly appreciated!

Try the below code:

Dim FirstBlankCell As Range

    Sheets("Sheet1").Select
    Range("b6:r6").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Set FirstBlankCell = Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
    FirstBlankCell.Activate
    Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
On Error GoTo 0
    Set FirstBlankCell = Nothing
    End Sub

Hi gujugof -

Operationally it works perfectly. Any way to add a line which then deletes the text (while leaving the formating alone) from the range b6:r6 on sheet1 once the data has been copied to sheet 2?

You can add the following and see if that works.

Sheets("Sheet1").select
activesheet.range("b6:r6).clearcontents

Thanks gujugolf. Works perfect!