Excel – Copy to next blank line

January 2017



<!-- TOC -->

Issue


I'm new to macro - so don't laugh... I'm trying to copy Cells A1 and B1 from 400 worksheets to a single worksheet (Merge). The following macro code copies and pastes the cells but overwrites each time the macro is run.

How can I tell the system to paste in the next blank row?


Sub Macro5() 
' 
' Macro5 Macro 

' 
Range("A1:B1").Select 
Selection.Copy 
Sheets("Merged").Select 
Range("A1").Select 
ActiveSheet.Paste 

End Sub

Solution


You can use this:
Range("A401").End(xlup).Select

Note


Thanks to Excelguru for this tip on the forum.

Related


Published by aakai1056. Latest update on January 24, 2010 at 10:54 AM by aakai1056.
This document, titled "Excel – Copy to next blank line," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).