Excel - Auto next consecutive number

Hello Waqar

The code below should do what you want.(click Select All and copy into your workbook).

Note that (in the first bit in bold) I've used a With loop (so the correct sheet is altered no matter which sheet the macro is run from) and that all the clear actions can be combined into the next (single) line of code. I didn't clear C2 (as you did) since the next line overwrites it... 

The bit between the second and third bold bits will locate the last cell in Data and the Entry cell C2 becomes that value plus 1.

Sub ClearButton()

With Worksheets("Entry")
    .Range("L1:L2, B3, J5:K5").Clear
    .Range("C2") = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Value + 1
End With

End Sub

If you're sure you (or other users) will always run the code from the Entry sheet, you could skip the With loop and use this (the bit line in bold being the essential addition to your original code) :

Sub ClearButton()

Range("L1:L2, B3, J5:K5").Clear
Range("C2") = Worksheets("Data").Range("A" & Rows.Count).End(xlUp).Value + 1

End Sub

Hope this helps.If not, please let me know. If so, please Select the answer.