Sunday, December 30, 2018

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

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

No comments:

Post a Comment

plot explanation - Why did Peaches' mom hang on the tree? - Movies & TV

In the middle of the movie Ice Age: Continental Drift Peaches' mom asked Peaches to go to sleep. Then, she hung on the tree. This parti...