Excel – Copy to next blank line

December 2016



<!-- 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 :

This document entitled « Excel – Copy to next blank line » 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.