auto serial number

[itzik]

Hello , when updating a report in a workseet I want that each time I am activating the worksheet a new serial number will be automatically generate to the report.
The first serial number will be 1000/3000 the second 1001/3000 the third 1002/3000 and sow on
Thank you
itzik

[Bob Phillips]

See http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

[rbrhodes]

Hi Itzik,

New member!

Problem: The serial no. is not a number. So the questions that follow are:

- Is it always 4 digits "/" 4 digits?
- Always start at 1000?
- What happens at 9999?
- Does only the first 4 digit number increment?

[mdmackillop]

1000/3000 can be used as a number if custom formatting is used as in 0000"/3000".

[Bob Phillips]

It seems to me that only the first part was autonum, the second was static.

[rbrhodes]

Answers that question! Need the OP to answer the others tho, I think.

[itzik]

hi rbrhodes
- Is it always 4 digits "/" 4 digits?
the first 4 digit number wiil increment ‏‎until 9999 . at that point a new sequence will start as 1000/3001 > 1001/3001 >1002/3001

thank you
itzik

[rbrhodes]

Hi itzik,

OK now we can look at it:


Starts at:

1000/3000
1001/3000 (first 4 digits goes up by 1)
1002/3000 etc

9999/3000 First 4 turns into 1000 again and

1000/3001 second 4 digits goes up by one
1000/3002
etc
1000/9999

Probably enough...

[itzik]

[rbrhodes]

Try it out. It's stored on Sheet (1) A1 and put to Sheet (2) A1 change that as you will...

[VBA]
Option Explicit
Private Sub Worksheet_Activate()

Dim First4 As Long
Dim SerNum As String

'Serial number has to be kept somewhere.
' Right now it's on Sheet1 in Cell A1 and this will get it

SerNum = Sheets(1).Range("A1")

'Get First 4 digits
First4 = CLng(Left(SerNum, 4))

'Check if first 4 at 9999 yet
If First4 < 9999 Then
'Add 1 to First 4
SerNum = First4 + 1 & Right(SerNum, 5)
'Store it
Sheets(1).Range("A1") = SerNum
'Put it
Sheets(2).Range("A1") = SerNum
Else
'Add 1 to Second 4
SerNum = "1000/" & CLng(Right(SerNum, 4) + 1)
'Store it
Sheets(1).Range("A1") = SerNum
'Put it
Sheets(2).Range("A1") = SerNum
End If

End Sub
[/VBA]

[itzik]

Thank you all
That what I did
1. According to mdmackillop i changed the custom formatting to 0000"/3000"[/font]

2. the line in Rbrhodes code SerNum = First4 + 1 & Right(SerNum, 5)

Changed to SerNum = First4 + 1 & Right(SerNum, 0)

It work perfect

‏‎again thank you all

itzik

[mdmackillop]

Hi itzik,
Using custom formatting is not required by DR's code, which deals with the text of numbers, rather than their value.

If you were to custom format Column A as described, you can enter numbers as normal, ie, enter 1000, 1001 etc. and add your increment as in =A1+1. It will not though, handle the 9999 to 1000 change

[itzik]

yes but i need the number to display‎‏ as 1000/3000

[mdmackillop]

Which it does.

[itzik]

that what i did

[mdmackillop]

If you have a problem with the solution, please post your workbook.

[itzik]

Hi
The workbook attached
The code that Rbrhodes suggested and that I modified a bet work ok.
But it work only on sheets that are specified in the code, sheet 1 to 3.
I want it to work automatically on each sheet that I will add in the ‏‎future.
Can you help me whit it?
Itzik

[mdmackillop]

I want that each time I am activating the worksheet a new serial number will be automatically generate to the report.

This implies that every time you open a sheet, it will add an incremental number.
Your example implies each sheet has an incremental number in A1.
We can do either or both, but we need a much clearer idea of what you are after.

[itzik]

This implies that every time you open a sheet, it will add an incremental number.

this is what Iam after.
itzik

[mdmackillop]

.....we need a much clearer idea of what you are after.

Last request for proper information.