find last cell and copy data

[zangel]

I need some help to write a macro.

I have two Excel sheets. One is the form. The other one is the
database.

When I run the macro, I want Excel to find out the last cell in third
column (ex: C5). In C5 the value is 005. Then, move down one row (now
in C6). I want to add in C6 the value of cell C5 + 1. So I want the
value 006 in cell C6.

Then, I want to copy the value of C6 (new last cell) to the other sheet
in cell A4.

Next time, I run the macro, I want Excel to find out that C6 is the
last cell and put 007 in cell C7. Then, copy 007 in cell A4 of the
second sheet.

Can someone help?

 

[L. Howard Kittle]

Hi zangel,

Try this. Change C100 to a larger number if you need more than 100 rows in
column C.

Sub AddAndCopy()
Range("C100").End(xlUp).NumberFormat = "000"
Range("C100").End(xlUp).Offset(1, 0).NumberFormat = "000"
Range("C100").End(xlUp).Offset(1, 0). _
Value = Range("C100").End(xlUp) + 1
Range("C100").End(xlUp). _
Copy Sheets("Sheet2").Range("A4")
End Sub

HTH
Regards,
Howard