MrExcel Publishing
Your One Stop for Excel Tips & Solutions


Posted by RONNIE on August 29, 2001 7:41 AM

How can I let data from for example "sheet1" cell "A1" be saved to "sheet2" the first empty cell in column "A"? So if there's information in cell A1 to A10 that he will automatically save it in cell A11? And next time save it to cell A12! Does anybody knows how I can do that? Thanks in advance!!!


Posted by Damon Ostrander on August 30, 2001 9:07 AM

Hi Ronnie,

The range object's End method is very useful from VBA for finding the last cell in a range that contains data. It is equivalent to Ctrl-arrow key. Here is how you would use it to do what you described:

Worksheets("Sheet2").[A65536].End(xlUp).Offset(1).Value = Worksheets("Sheet1").[A1].Value

This simply puts A1 from sheet 1 into the cell 1 row down from the first cell found containing data on a search up from the last cell in column A on sheet 2.

Happy computing.