Macro to increase serial number by one after each print cyle

[MATTN]

Hello all,

I'm working on an Excel spreadsheet where I need a serial number to increase by one each time I print the document. For example in cell B6 I have serial number RAIL-0001. I would like to set it up so that the next time I print the number will change to RAIL-0002. Is this possible? Any help would be greatly appreciated. Thanks for having me!

[JOHN H. DAVIS]

Without seeing your current code, for sequential numbering I normally use a method like below.

I place on a separate helper sheet (in this case Sheet3) Column A numbers beginning with 1 to whatever.

The following code will increase the value everytime you run the code. Perhaps you could use this idea.

Please Login or Register  to view this content.

[ducky_yeng]

I gave it a go and came up with the following code below. It was tried and tested in Excel 2010.
Wonder if it meets your requirements.
Note that you can merge some lines of code if you want to make it more compact and remove the debug.print where applicable

Please Login or Register  to view this content.

[MATTN]

Thanks John and Ducky, I'm lost on where to paste the code. Does it go in the "fx" line when I highlight the cell? I've seen other post that say the code has to go in the visual basic editor under the developer tab. Please bare with me. Codes and Macros have never been my strong point in Excel. Thanks again for your help!

[ducky_yeng]

Q. Where to paste the code?
A. Yes, Paste in Visual Basic Editor.

1. On ribbon click Developer
2. On toolbar click Visual Basic
3. From Project View (left hand side) click ThisWorkBook
4. In code window (right hand side) paste the text

Just noticed that both John and I have only addressed the increment of the serial number and not automated the process of printing.
Are you looking for code to print out the whole workbook / particular worksheet and then increment the serial number?

[MATTN]

Thanks. If the serial number increased by one every time I print the workbook that would be perfect versus having to manually change the number everytime I print. I wasn't sure if it is possible to make this work using a serial number with a combination of letters, a dash, and then the number. Only the number needs to change. ie "RAIL-0001" when printed would change to "RAIL-0002". I hope this makes sense.

Matt

[jeffreybrown]

Untested (with a print), but try...

Go to the VBE >> Alt + F11

Please Login or Register  to view this content.

This code goes in the ThisWorkbook module

[ducky_yeng]

Based on your clarification and suggested code by jeffreybrown. I suggest that you merge the code as follows:

Please Login or Register  to view this content.

I have kept function to update the serial number out of Workbook_BeforePrint procedure but you are welcome to merge all the code together.

The reason that I suggest using the IncrementSerialNumber() function is because:
- it can increment any serial number provided it is in the following format: sometext-####
- if the number is RAIL-0009 then it will be incremented to RAIL-0010 opposed to RAIL-00010

Do try both our suggestions to see which one fulfils your requirement.
Note: due to time constraint, I haven't been able to test the code changes.
Hope that helps.

[MATTN]

Run-time error '9': subscript out of range is what I see when attempting to print. Here what I am doing and maybe you can see what I'm doing wrong. I open the workbook click the cursor on the cell that I want the serial number in (blank), I then go to the VBE and click on this workbook and past the entire code that you provided. From there I'm not sure what to do. Do I have to save it as something and go back to the workbook and load it using the "Macros" button? Thanks for sticking with me on this.

[jeffreybrown]

Hi Matt,

Good idea to say who you are responding too, myself or ducky!

[MATTN]

My fault I was responding to both. Same result for both macros which leads me to believe that I am doing something wrong on my end. Thanks for your patience.

[jeffreybrown]

Hi Matt,

It works for me, but maybe you can explain what you are doing?

Here is what I did.

Took the code I posted in post #7 >> Opened up the VBE >> ThisWorkbook module >> Paste macro on right side of screen

In cell B6 >> RAIL-0001

As soon as I print that worksheet >> B6 changes to RAIL-0002

[MATTN]

Thanks Jeffrey,

Here is what I did.

Copied the code from #7>>opened the VBE>>this workbood module>>pasted the macro on the right side of the screen>>RAIL-0001 in cell B6>>when I hit print It gave me the runtime error'9': subscript out of range. I ended it and the workbook printed but with cell B6 empty. I'll open a new workbook and try the same to see if I have any luck. Thanks again.

[MATTN]

Jeffrey,

I tried your code on a blank workbook and it worked perfectly so maybe the problem is in my workbook itself. Hmmmmm Any suggestions?

Thanks,

Matt

[jeffreybrown]

The only suggestion I have is to make sure no other code is interfering, and if so, post your workbook here.

[MATTN]

Hi Jefferey sorry for the delay. I don't see any other code so I'm posting the workbook for you to look at. As always thaks for your help!

[jeffreybrown]

Hi Matt,

Well first off, there is no code in this workbook????

Place this code in the ThisWorkbook module

Please Login or Register  to view this content.

I see on sheet blank (3), in cell B6 you have RAIL-0001.

I placed the code above in the appropriate place within the workbook and instead of printing, just to test, on the keyboard I selected ctrl + p which opens up the print dialogue box. At that time you should see B6 change from RAIL-0001 to RAIL-0002.

[MATTN]

Hi,

I realize there was no code in that workbook. I sent it to you without for you to try. Here it is again with the code allthough most likely done incorrectly on my end seeing as though it still doesn't seem to work. Thanks for your patience.

[jeffreybrown]

Hi Matt,

Again, this is ThisWorkbook module code, not general code. You have the code in a general module.

Open up the VBE, find ThisWorkbook on the left side of the screen, double click to open and paste the code on the right side of the screen.

http://www.rondebruin.nl/code.htm

[MATTN]

It works! Sorry it took me so long to get it. Can't thank you enough for the support!

[jeffreybrown]

Quite alright, yes glad you now have a working project.

Your welcome and thanks for the feedback.

=======================================================================

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

[MATTN]

Okay marked as Solved. If you don't mind one more quick question and I will leave you alone. How do I modify the code so that if I printed lets say 10 or 20 copies that they would still increase by one?

[jeffreybrown]

Wouldn't you just select 10 or 20 copies and that equals one print?

The next time you hit print the code would fire again.

[MATTN]

Yes, however I need each page to have its own serial number. So if I were to select 10 copies all ten would be marked the same vs RAIL-0001, RAIL-0002, RAIL-0003 etc. For example the numbers could eventually be as high as RAIL-8000 someday.

[JBeaucaire]

Use a separate macro for that.

Please Login or Register  to view this content.