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

 

[vaskov17]

Sounds like the macro is not running when you are closing the file. Make sure the code from my post above is in the "ThisWorkbook" section in the VBA editor.

 

[TheShyButterfly]

The code was definitely placed in the "ThisWorkbook" in the VBA editor and saved as a macro template.

I have since tried it again and when I change the system date, the first job number changes but the others remain the same original job number of the previous month. So it almost works

 

[vaskov17]

I'll run some tests but I don't think I've run into that problem. Try setting up a new template with the macro and formula and see if anything is different in sheets based on that one.

 

[TheShyButterfly]

Hi Vaskov17

I have created a brand new macro enabled workbook, and entered in the formula (copied & pasted) into Cell A1 and filled down to row 200.

I saved the workbook and closed it.

I double clicked on the template which opened the workbook. I entered data in to column B (about 10 rows of it), and it generated the job number in the exact format that I requested.

I saved the workbook as macro enabled workbook.

I changed the system date on my computer for another month. I then opened the workbook (as I would be doing if I was to do my analysis). The job number in A1 changed to the new month, but the other job numbers remained the same original job numbers before the system date change.

Having posted another request regarding another issue .. where it was a suggestion to delete the 'Option Explicit' at the top of the VB window, I thought that perhaps that was the problem ... and I went through the whole process again, but I had the same result. It still changed the job number in A1 but left the other job numbers with the original content.

My macro security is set to "Disable all macros with notifications" ... I had no notifications asking if to enable or disable macros.

So, I am at a loss at this point.

Cheers,
TheShyButterfly

 

[Jim885]

The reason cell A1 keeps updating each time the file is opened is because the VB code is designed to change (hard code) the Job Numbers beginning at the second row (A2), then down the column. Thus, always keeping the formula in cell A1 which will always update to the curent system date.

I think a small adjustment to the code will correct for this. Or, you can simply begin your sequence of numbers from cell A2, and hide row 1.

Option Explicit should remain at the top of the code.

 

[Jim885]

Correction to above ...
Option Explicit should not remain at the top. I was typing slower than I was thinking, and omitted the 'not'

Also, vaskov17 wrote this in his post #7 ... Basically what the code does is when the file is being closed it looks in cells in "Sheet1" starting at A2 and going down.

He also wrote ...
1. In the template file use the formula in cells A2 down as far as you want:
=IF($B2="","","SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW()-1,"0000"))


His solution will work for you.

 

[vaskov17]

Like Jim said both the macro and the formula work correctly starting on the second row. If you put the formula in A1 the first job number will end in 0000 and the macro will not touch it and it will always update. If you don't have a header row and your first job is in the first row use the below formula and code:

Formula:

=IF($B1="","","SRC" & YEAR(TODAY()) & TEXT(MONTH(TODAY()),"00") & "-" & TEXT(ROW(),"0000"))


Macro:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim i As Long
    
    With Worksheets("Sheet1")
        If .Range("A" & Rows.Count).End(xlUp).Row > 0 Then
            For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
                If .Range("A" & i).Value <> "" Then
                    .Range("A" & i) = .Range("A" & i).Value
                Else
                    Exit Sub
                End If
            Next i
        End If
    End With
End Sub

 

[vaskov17]

also Option Explicit basically forces the declaration of all variables and it shouldn't make a difference if it's there or not, unless one of the variable names is misspelled somewhere

 

[Gswanie]

I am still learning Excel, so please tolerate me.
I don't know why you need VBA, or the advantages of it yet.
I loved the adapted formula of Shybutterfly =IF($B1="","","SRC"&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"-"&TEXT(ROW()-1,"0000"))
My advice would be to add a Capture Date column and then use that date's Year and Month concatenated in your code in the place of the TODAY() Function.

 

[vaskov17]

I am still learning Excel, so please tolerate me.
I don't know why you need VBA, or the advantages of it yet.
I loved the adapted formula of Shybutterfly =IF($B1="","","SRC"&YEAR(TODAY())&TEXT(MONTH(TODAY()),"00")&"-"&TEXT(ROW()-1,"0000"))
My advice would be to add a Capture Date column and then use that date's Year and Month concatenated in your code in the place of the TODAY() Function.

That's a possibility but it's another piece of information the user is responsible for which opens things up for errors and wasted time. My understanding of the problem was that the numbering should be automatic without any user involvement in the process.