PDA

View Full Version : auto serial number



itzik
08-22-2009, 08:52 AM
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 :mkay

Bob Phillips
08-22-2009, 09:13 AM
See http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

rbrhodes
08-22-2009, 03:12 PM
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
08-22-2009, 03:28 PM
1000/3000 can be used as a number if custom formatting is used as in 0000"/3000".

Bob Phillips
08-22-2009, 03:32 PM
It seems to me that only the first part was autonum, the second was static.

rbrhodes
08-22-2009, 03:32 PM
Answers that question! Need the OP to answer the others tho, I think.

itzik
08-22-2009, 08:12 PM
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
08-22-2009, 10:39 PM
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
08-22-2009, 11:05 PM
:yes:friends:

rbrhodes
08-23-2009, 12:07 AM
Try it out. It's stored on Sheet (1) A1 and put to Sheet (2) A1 change that as you will...


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

itzik
08-23-2009, 03:25 AM
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
08-23-2009, 03:44 AM
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
08-23-2009, 03:59 AM
yes but i need the number to display‎‏ as 1000/3000

mdmackillop
08-23-2009, 04:05 AM
Which it does.

itzik
08-23-2009, 04:14 AM
that what i did

mdmackillop
08-23-2009, 04:18 AM
If you have a problem with the solution, please post your workbook.

itzik
08-23-2009, 10:48 AM
Hi
The workbook attached
The code that Rbrhodes suggested:clap: 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:beerchug:

mdmackillop
08-23-2009, 11:29 AM
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
08-23-2009, 12:07 PM
This implies that every time you open a sheet, it will add an incremental number.

this is what Iam after.
itzik

mdmackillop
08-23-2009, 01:02 PM
.....we need a much clearer idea of what you are after.
Last request for proper information.

GTO
08-23-2009, 01:05 PM
Greetings itzik,

I'm at home where I only have xl2000, so I couldn't open your example.

I get the part that as a new/blank sheet is added, you'd like the same incremental tracking to start for the new sheet.

Does the incremental number get stored on each sheet, or does it get stored on a seperate "report" sheet?

Mark

itzik
08-23-2009, 01:38 PM
I want taht when I open a workbook in the first sheet will start an
incremental number for ‏‎reports in cell A1.
in each sheet one ‏‎report
the first number will be 1000/3000
second sheet in cell A1
1001/3000 ( only the first 4 digits goes up by 1)
1002/3000 etc

when it get to 9999/3000 First 4 turns into 1000 again and second 4 digits goes up by one 1000/3001

i want taht when i open a new sheet or when i am addition new sheet to the workbook, the incremental number will continue ‏‎automatically‎‏
the number ne

itzik

rbrhodes
08-23-2009, 03:07 PM
Looks like the OP missed ...an attachment...the point... Well just missed!

I I understand correctly:

- The numbering starts on 1000/3000
- Each sheet from there goes up by 1, to 9999 then the change
- Adding a sheet should genereate a new number as above

So, what I would do, presuming you can use Sheet 1 as storage ONLY:

1) Open the Workbook with 3 sheets

2) Leave the A1 formatting as 'General' (or put it back if changed)

3) Put 1001/3000 in the desired cell on sheet 2

4) Put 1002/3000 in the desired cell on Sheet 3 and in A1 on Sheet 1

5) Name sheet 1 Counter or something

5) Hide sheet 1 so it doesn't get used except for storing the number

6) Add this code to the ThisWorkbook module. Change the sheet 1 name if needed

Now when you add a new sheet the number is incremented and added to the new sheet. The code specifies A1 but it can be put anywhere on the new sheet of course.

Don't use change or delete Sheet(Counter) and you should be fine

mdmackillop
08-23-2009, 03:44 PM
I wonder about the rational of the question. While apparently there is not a numerical limit to the number of sheets in a workbook, I suspect 9000+ will start to cause memory issues

rbrhodes
08-23-2009, 05:08 PM
Hi,

I looked at the math too. 9999/9999 is a lot of invoices !

itzik
08-23-2009, 10:51 PM
Rbrhodes thank you, it work perfectly when i am adding sheets.

but after adding new sheet that ‏‎numbered‎‏ as 1005/3000 and deleteing it, the cuonter don't subtract‎‏ to 1004/3000!

‏‎therefore the next sheet taht will be add will be numbered as 1006/3000 and not as it required to be 1005/3000

can you help with this

itzik

Aussiebear
08-24-2009, 03:10 AM
Where will the sheets be normally stored? If they are in the workbook, then we could check to see if the worksheet exists, and if not then subtract one from the count.

itzik
08-24-2009, 03:54 AM
what is ‏‎recommended‎‏ as max sheet in woekbook

the sheets stored in the workbook.

itzik

mdmackillop
08-24-2009, 05:09 AM
but after adding new sheet that ‏‎numbered‎‏ as 1005/3000 and deleteing it, the cuonter don't subtract‎‏ to 1004/3000!
A new twist. What if you delete an earlier sheet, eg 1002/3000. How do you renumber for 1003 to 1004 etc.? Better not to allow sheets to be deleted, but to Clear Contents only.

itzik
08-24-2009, 01:48 PM
thank you all

itzik

geekgirlau
08-24-2009, 09:34 PM
Just to throw another idea into the mix :devil2:

I can't open the attachment here (Office 03, no administrator access to load compatibility pack), but from the comments I'm assuming that essentially we have a series of invoices, with an incrementing invoice number.

My question is, do you REALLY need to have a separate sheet for each invoice?

Now obviously you want a nice display for printing, but what about something like the following:

1 sheet with the invoice details in a list format (incrementing number, description, client details etc.) - 1 row per invoice.
1 template sheet with the desired layout
Procedure to take the details from the selected row(s) and apply them to the template sheet for printing purposes
Perhaps an additional procedure to clean up any "print" sheets on closingMuch smaller in size and WAY simpler to navigate.

itzik
08-24-2009, 10:59 PM
that a nice concept i will try it

itzik

geekgirlau
08-25-2009, 12:43 AM
Essentially this is more of a mini-database concept. You have your data, then you have information that you need to report. Most of the time you will NOT need to report ALL the information at once. This also allows you to easily analyse your data, in terms of $ invoiced per quarter, per client etc.