Copy and paste to another sheet first empty row, pastes over the previous row if first cell is empty

Question

I'm copying a range from one sheet (B14:I14) and pasting the values to another sheet in the first empty row. This works fine if the range has data in the first cell (B14).

When there is data in some of the cells but not B14, the next time it pastes over the same row as the last time I executed the macro.

The range will vary from having all cells containing data or just two. I need it to check that target row is empty, not just the first cell.

Here is my macro:

Sub Save7()
    Dim NextRow As Range
    With Sheets("Sheet3")
        Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    Sheet1.Range("B14:I14").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
End Sub

Alex D Alex D · Accepted Answer · 2014-01-24T19:46:05

I'd suggest using this:

Sub Save7()
    Dim NextRow As Range
    Set NextRow = Range("B" & Sheets("Sheet3").UsedRange.Rows.Count + 1)
    Sheet1.Range("B14:I14").Copy
    Sheet3.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
End Sub

That should work well. UsedRange checks across all columns so if B is empty it won't overwrite it. Only problem is if the 1st row is blank, but you can just add header data there, or something and it won't be bothered by it ever again. When the first row is blank UsedRange.Rows.Count still outputs 1. Enjoy!

Copy and paste to another sheet first empty row, pastes over the previous row if first cell is empty

1 Answers