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.
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.