Excel - A Macro to copy and paste data in next blank cell
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
Related
- Paste data below the last used row
- Paste data below the last used row vba - Best answers
- Paste data below the last used row macro - Best answers
- Paste data after last row vba ✓ - Forum - Excel
- How to paste data in alternate rows in excel - Forum - Excel
- Copying & pasting data in next available row in same worksheet. ✓ - Forum - Excel
- How to copy and paste data from one excel sheet to another automatically - How-To - Excel
- Vba code to copy and paste data from one workbook to another - How-To - Excel
This document, titled « Excel - A Macro to copy and paste data in next blank cell », is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).
Subscribe To Our Newsletter!
