VBA - Generate Serial Number max in two sheets

[elbazi]

Hi guys,

have serial numbers with data in row in two different sheets (from main sheet divided later to two different, according the data in row). can someone help with a code to generate serial number after checking what is the max in the whole workbook? i am currently using +1 on the last row with data, but obviously its not correct seeing that its not necessary the max.

 

[bebo021999]

again:

Option Explicit
Sub NewSerialNumber()
Dim lr1&, lr2&, i&, cell As Range, max&, cID&, ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("LOG")
Set ws2 = Worksheets("STORE")
    lr1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    lr2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
    For Each cell In ws1.Range("A1:A" & lr1) 'loop column A of LOG, to get max
        If cell Like "GR-*" And Right(cell.Value, 6) > max Then max = Right(cell.Value, 6)
    Next
        For Each cell In ws2.Range("A1:A" & lr2) ' then, 'loop column A of STORE, to get final max
            If cell Like "GR-*" And Right(cell.Value, 6) > max Then max = Right(cell.Value, 6)
        Next
    ' generate new ID into last row of STORE?
    ws2.Range("A" & lr2 + 1).Value = Format(max + 1, "GR-000000")
End Sub