Ask a question »

Excel - A Macro to copy and paste data in next blank cell

April 2016


I have two sheets in a workbook. Sheet1 is for summary and Sheet2 is for the raw data. I need to update/insert/copy data in Sheet2 to Sheet1. Below is the sample macro code:
Sub Summarize()   
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _   
False, Transpose:=False   
End Sub

The code above is working but everytime I change data in Sheet2, the Sheet1 only replace its data. What I need is everytime I change data in Sheet2, it should be added (append) in Sheet1.


You need to know the last used row. Here 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

Thanks to rizvisa1 for this tip.

For unlimited offline reading, you can download this article for free in PDF format:

See also

In the same category

Published by aakai1056. - Latest update by deri58
This document entitled « Excel - A Macro to copy and paste data in next blank cell » from CCM ( is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.