Auto-incrementing item reference number over multiple sheets

**notwen** · 02-10-2012, 04:12 AM

Hello All,

I have a set of sheets where I put in various types of call centre data - one line item per call. The sheet where this line is entered depends on the type of call.

I would like to have a unique number assigned to the line item when that line is filled (regardless of the sheet on which I happen to be). I think that the easiest thing would be to have a sheet containing the base number from which that unique item number is calculated. For example, I begin answering a call. I enter the date on the next empty line in the sheet according to the call type. Tabbing over to the next cell (column C) will pull the reference number from the base number sheet and put it in the first cell of the line I'm entering. The base number is incremented and overwritten on the base number sheet. I continue to fill in the line. For the next call, I may go to the next empty line on that sheet or it may be another sheet. However, entering a date in column B would still cause the reference number to be automatically entered into that line's column A with the number on the reference number sheet to be auto-incremented.

So, if anyone understands what I'm trying to say, how could I go about doing this?

Cheers,
Notwen

**arlu1201** · 02-10-2012, 04:49 AM

Do you have some sample data that you can upload?

**JBeaucaire** · 02-10-2012, 04:55 AM

Here's an example of one way to do it with no VBA. The CodeSheet has a named range cell on it named NextNum, that cell is watching column B on the other sheets and finding the max current number then adding one to display. Then that named cell is used in Data Validation on the other sheets so any cell you click in and use the drop down will display the "NextNum". If you use the drop down in one cell, in the next cell it will offer a new number.

**Domski** · 02-10-2012, 05:03 AM

My effort...

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngLoopRange As Range
    On Error GoTo ErrorHandler
    Application.EnableEvents = False
    For Each rngLoopRange In Intersect(Target, Range("B2:B" & Rows.Count))
        If rngLoopRange.Offset(0, -1) = "" And rngLoopRange <> "" Then
            With Sheets("Control").Range("Next_Ref")
                rngLoopRange.Offset(0, -1) = .Value
                .Value = .Value + 1
            End With
        End If
    Next rngLoopRange
CleanExit:
    Application.EnableEvents = True
    Exit Sub
ErrorHandler:
    MsgBox Err & " - " & Err.Description
    GoTo CleanExit

End Sub

In the example the code at the moment goes on each sheet's code page that it is to run on but if it was to run on every sheet other than the control one it could be set up as workbook level event.

Dom

**notwen** · 02-10-2012, 05:37 AM

Thanks to all for their input so far.

Thank you Domski. Your code works well but you're at a level beyond my humble abilities. I can create the worksheet and workbook level coding but it's the functions you are using that baffle me at the moment. I also need to be able to pad the number and put a prefix on it.

Maybe I can make things simpler.

How about I use a reference number on a per sheet basis? I've attached a simple spreadsheet. When I enter the date on the next empty line, the reference number is auto-generated using the base number (upper right, in yellow). As with Domski's code, there should be no user intervention wrt that ref number. That base number is auto-incremented once used.

Simpler?

Thanks,
notwen

**JBeaucaire** · 02-10-2012, 05:59 AM

Open the VBEditor and remove the code from the individual Sheet modules, if there is any.

Now open the ThisWorkbook module and put this global macro in...

Option Explicit

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
Dim cell As Range

    Application.EnableEvents = False
    With sh
        If IsNumeric(.[E1]) Then
            For Each cell In Target
                If cell.Column = 2 Then     'column B was changed
                    cell.Offset(, -1).Value = "S" & sh.Index & Format(sh.[E1], "-0000")
                    sh.[E1] = sh.[E1] + 1
                End If
            Next cell
        End If
    End With
    Application.EnableEvents = True
End Sub

This macro will work automatically on every sheet if there is a NUMERIC value in cell E1 of that sheet... a code will be entered anytime you enter a value in column B.