Ask a question »

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

September 2015


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




Issue


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()   
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

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.

Solution


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:
Excel-a-macro-to-copy-and-paste-data-in-next-blank-cell.pdf

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 (ccm.net) 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.