Getting sheetname and using for auto incremental ID

Dineshja48 

To achieve the desired functionality of automatically generating incremental work item IDs based on the sheet names in Excel, you can use a combination of formulas and VBA macros.

Here is a step-by-step guide:

1. Open the Visual Basic for Applications (VBA) editor by pressing ALT + F11 in Excel.
2. Insert a new module by clicking on "Insert" > "Module."
3. In the module, paste the following VBA code:

Function GetWorkItemID(sheetName As String) As String
    Dim ws As Worksheet
    Dim counter As Integer
    
    counter = 1
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> sheetName Then
            If Left(ws.Name, 2) = Left(sheetName, 2) Then
                If IsNumeric(Mid(ws.Name, 4, 6)) Then
                    If Mid(ws.Name, 4, 6) = Mid(sheetName, 4, 6) Then
                        counter = counter + 1
                    End If
                End If
            End If
        End If
    Next ws
    
    GetWorkItemID = Format(counter, "00")
End Function

 4. Close the VBA editor.

Now, you can use the custom function GetWorkItemID in your worksheet to automatically generate work item IDs based on the sheet names.

Here is how you can implement it:

1. In the cell where you want to display the work item ID, enter the following formula:

=GetWorkItemID(SUBSTITUTE(TEXT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),"000000"),"CH-",""))

Note: Replace "A1" in the formula with the cell reference that contains the sheet name (e.g., the cell that contains CH-2306011).

This formula uses the SUBSTITUTE function to remove "CH-" from the sheet name, converts the remaining numeric part to text using the TEXT function, and then passes it to the GetWorkItemID function to retrieve the incremental work item ID.

Whenever a new sheet is added or the sheet names change, the formula will automatically update the work item IDs accordingly.

Make sure to save the workbook as a macro-enabled (.xlsm) file to preserve the VBA code.

Note: If you rename a sheet after copying, the table that holds the work item IDs may not update automatically. In this case, you can add a button or use a worksheet change event to trigger a macro that refreshes the table. Let me know if you need assistance with implementing that as well.

The steps were processed with the help of AI. 

All information always without guarantee.

It is always recommended to make a backup.