Automatically create job number based on prefix, year,month and entry number

[TheShyButterfly]

Hello and thank you for taking your time to read this post.

I have tried searching the internet and this forum but have not found anything that I have been able to modify to my needs.

So here is what I need help with.

I have a spreadsheet (excel 2010) with multiple columns - for example, Job#(A1), Event type(B1),KPI data (C1),SNP Order# (D1), ... etc ... there is so much information that a new workbook is derived from a template each month.

I need VBA code (or macro) in Column A2 which will increment with prefix 'SRC' then the 'year' 'month' and then increment by 1 so the job number would look like this:


SRC201309-0001 (SRC Year2013 month09 - JobNumber0001)

As the spreadsheet is saved as a template, it would be great if each time a workbook is created from this template, that it would automatically base the first job number on the year and month on that day and with first job number.

It would be appreciative if the code could include that if Column B has empty field that the Column A field remain blank until data is entered in Column b field (I'd imagine for example that the IF statement would kick in there .... eg. if(B2="","", and here is where the job number would be formulated ....


[TABLE="class: grid, width: 12"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Job #
[/TD]
[TD]Event Type
[/TD]
[TD]KPI Data
[/TD]
[TD]SNP Order#
[/TD]
[TD]Weather
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Earthquate
[/TD]
[TD]87
[/TD]
[TD]12112
[/TD]
[TD]Severe
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD][/TD]
[TD]Theft
[/TD]
[TD]93
[/TD]
[TD]12113
[/TD]
[TD]Overcast
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

So that the sheet will look like this ...

[TABLE="class: grid, width: 13"]
<TBODY>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Job#
[/TD]
[TD]Event Type
[/TD]
[TD]KPI Data
[/TD]
[TD]SNP Order#
[/TD]
[TD]Weather
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]SRC201309-0001
[/TD]
[TD]Earthquate
[/TD]
[TD]87
[/TD]
[TD]12112
[/TD]
[TD]Severe
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]SCR201309-0002
[/TD]
[TD]Theft
[/TD]
[TD]93
[/TD]
[TD]12113
[/TD]
[TD]Overcast
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]


I hope I have provided enough information and not confused you.



Thanking you in advance
Theshybutterfly

 

[TheShyButterfly]

Thank you Jim, I didn't think of hiding the first row ... but that is an option if all else fails .....

Thank you for your contribution ... I appreciate the various perspectives, when one has a problem it is normal to just a have a 'tunnel' outlook and not able to 'think outside the square'.

Cheers,
Ingelise

 

[TheShyButterfly]

Thank you all for your contributions ... it was strange because I hadn't caught up with all the posts til just now... but after I re-read the code that Vaskov17 wrote in post #7, I thought I'd change the 2 to a 1 ... and tested it out and was ecstatic to see that the job number remained the same But then I read Vaskov17's response in Post #17 .... I felt good that I thought the same thing.

A special thank you to you Vaskov17 ... thank you for persevering in your quest to resolving the problem, and in such a very timely manner.

I'm sure others will be able to use and adapt your code for their projects

I can now 'give you the day off'

Best wishes and gratitude,
TheShyButterfly