Auto Increment Multiple Sheets

Hi All,

My question is, how can I auto increment numbers across multiple worksheets.

I have 22 worksheets each sheet has 8 columns, Column A would be the column that when filled (With Text) would generate an auto number in column H2.
now this needs to happen no matter what sheet I enter data into, the Auto numbers would be added. Preferably I would like a format like: rcp-00001

I also have a userform that loads when the work book opens, this userform has 22 tabs (multi tab page), each tab has text boxes and a combobox, this are populated from each work sheet.
so I guess this needs to be taken into consideration for code, as I could end up with ambiguous name. From the userform I can fill the textboxes and save to the corresponding sheet.
so I already have quite a bit of code going on. All of my worksheets have there own name, although I don't think this matters as they can still be refered to as Sheet1, Sheet2 etc etc.

so in a nut shell if I enter data into sheet1 starting at A2 across to F2 this would auto generate a number in H2 ie (rcp-00001)
then if I entered data into sheet 12 at A2 to F2 it would generate a number in H2 ie (rcp-00002)

I hope my explanation is understandable.
thanks for any advice or help in advance.

Add this code to the Thisworkbook tab in the VBA editor.

Please Login or Register  to view this content.

remember to save as .xlsm

Thanks I will try this and let you know the outcome

Ok I put this code into a blank workbook and it operates perfectly.
I added the code to my workbook and I get nothing. not quite sure way below is my code for thisworkbook.

with your code added see bottom I have a workbook open event would this conflict?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim MaxNumber As Long
Dim Sheet As Worksheet
If Target.Column = 1 Then
For Each Sheet In ThisWorkbook.Sheets
For N = 1 To Sheet.Cells(Rows.Count, 8).End(xlUp).Row
If Left(Sheet.Cells(N, 8), 4) = "rcp-" Then
If Int(Right(Sheet.Cells(N, 8), 5)) > MaxNumber Then
MaxNumber = Int(Right(Sheet.Cells(N, 8), 5))
End If
End If
Next N
Next Sheet
End If
Application.EnableEvents = False
MaxNumber = MaxNumber + 1
Target.Offset(0, 7) = "rcp-" & Left("000000", 6 - Len(Str(MaxNumber))) & MaxNumber
Application.EnableEvents = True
End Sub



Option Explicit

Private Sub Workbook_Open()
Commandfrm.Show
End Sub

Try with Option Explicit at the top

and use this version where N is declared.

Please Login or Register  to view this content.