Problem with duplicated range names when copying worksheet

[ClaireS]

I am creating an automated work log/invoicing system for a motor repair company. Work is entered into a job sheet and I have various cells where I have set data validation using lists (customer ID, vehicle reg etc). These reference entries in Excel tables. For example, the Customer ID cell is validated using range name =CustomerIDs, and this name references =tblCustomers[ID]. (Data validation doesn't recognise Excel tables, hence the round-the-houses approach). These range names are scoped at the worksheet level.

My problem is this. The company obviously has many jobs on at any given time. I therefore have a hidden master job sheet. When a new job comes in, they create a Job Sheet for it by running a VBA macro to copy the master and number the copy with the next Job ID. All of this works fine. But I have noticed that all the range names used by my data validation lists are duplicated (scoped to sheet level), for each new job sheet. If I delete the duplicate names, the data validation drop-down lists still work fine, as they are pointing to the workbook scoped original name.

How can I stop these sheet-level dplicate range names from being created - or how can I dleete them in code?

 

[dmt32]

Hi,
Not fully tested but try following and see if it does what you want

Sub DeleteSheetLevelNames(Optional ByVal SheetName As Variant)
    Dim RangeName As Name
    For Each RangeName In ThisWorkbook.Names
        If InStr(1, RangeName.Name, "!") Then
        If Not IsMissing(SheetName) Then
            If RangeName.Parent.Name = SheetName Then RangeName.Delete
        Else
            RangeName.Delete
        End If
        End If
    Next RangeName
End Sub

call it after your sheet copy code.
You have option to specify the New Sheet name to limit deleting Sheet-Level names for that sheet only otherwise, they all get deleted.

 DeleteSheetLevelNames "Sheet1"

As always - MAKE A BACKUP of your workbook before testing new code.

Dave

 

[ClaireS]

Thanks Dave, that looks like exactly what I need, and so quick and simple. I'll give it a try - and I'll definitely back up first!

Claire
==

 

[dmt32]

Thanks Dave, that looks like exactly what I need, and so quick and simple. I'll give it a try - and I'll definitely back up first!

Claire
==

most welcome - hope it does what you want.

Dave