In this article we will show you an example of a Macro that can copy data in the next blank cell.

Example

If you have two sheets in a workbook: Sheet1 is for summary and Sheet2 is for the raw data and you need to update/insert/copy data in Sheet2 to Sheet1. Below is the incorrect sample macro code:

Sub Summarize()

Range("A6:AT6").Select

Selection.Copy

Sheets("ImprovementLog").Select

Range("B283").Select

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _

False, Transpose:=False

Range("B283").Select

End Sub

This code will work, but everytime you change data in Sheet2 the Sheet1 will only replace its data. But what you are probably looking for is that everytime you change data in Sheet2, it is also added (append) in Sheet1.

Solution

You need to know the last row used. In this case Range("B283"). Select finds the last used cell in column B and then select one cell below it:

lMaxRows = Cells(Rows.Count, "B").End(xlUp).Row

Range("B" & lMaxRows +1).select

Photo: © Everypixel