The following code will, when a new sheet is added, find the last value in column A of the previous sheet, add 1 to it and place this value in cell A1 of the new sheet.
Of course, it relies on macros being enabled when the new sheet is added.
Does this get you started?
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Const lCONTINUE_COLUMN = 1
Const lFIRST_DATA_ROW = 1
Dim lLastValue As Long
Dim wshLastSheet As Worksheet
Set wshLastSheet = Sh.Parent.Sheets(Sh.Index - 1)
With wshLastSheet
lLastValue = .Cells(.Rows.Count, lCONTINUE_COLUMN).End(xlUp).Value
End With
Sh.Cells(lFIRST_DATA_ROW, lCONTINUE_COLUMN).Value = lLastValue + 1
End Sub
Bookmarks