A few words of thanks would be greatly appreciated.

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

Latest update on October 19, 2020 at 04:01 AM by Irene Burn .

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

Example
Solution

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

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
Paste data to next empty row vba - Forum - Excel
Excel vba create new workbook and paste data ✓ - Forum - 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).

Excel - Remove #Value error message

Excel - Split data into several rows

1 vote - 5.0/5 1 Comment

Report

Anonymous User
Jan 5, 2021 at 03:30 PM

Your help is invaluable .... thank you so much

Example

Solution

Subscribe To Our Newsletter!