VBA Named Range Unable to Set Scope

Question

I am having trouble using VBA to set named ranges in my excel workbook. I have no problem setting named ranges, but no matter what I do they are only coming out with a worksheet specific scope. I need these ranges to have a whole workbook scope. I have followed several posts about scoping, but nothing has worked. The idea is that I have a worksheet called "Base" and it has several named ranges which are used throughout the workbook. At the click of a button I want to be able to delete the "Base" worksheet and replace it with a new copy(which comes from a hidden worksheet called "BaseTemplate"). I eventually need something similar to happen to the worksheet called "Crane". The "BaseTemplate" worksheet has all of the named ranges I need scoped only to that worksheet. What I am trying to do is delete all workbook scoped variables, delete the old worksheet, copy the "BaseTemplate"; rename the copy to "Base"; and convert the scope of the named ranges to workbook level. But I am stuck at the end. I can't seem to convert, or even create workbook level named ranges. Here is my code. It runs fine through the end, but the named ranges it creates are scoped to the worksheet. Thank you.

Dim rname As Name
For Each rname In ActiveWorkbook.Names
    If rname.Visible = True Then
        Debug.Print rname.Parent.Name
        If rname.Parent.Name = "Base" Or rname.Parent.Name = "JobEstimator.xlsm" Then
            rname.Delete
        End If
    End If
Next rname

For Each ws In ActiveWorkbook.Sheets
    If ws.Name <> "BaseTemplate" And ws.Name <> "CraneTemplate" And ws.Name <> "LotInspection" And ws.Name <> "LS2" And ws.Name <> "InvestmentSummary" And ws.Name <> "TemplatePage" Then
        ws.Delete
    End If
Next ws

Sheets("CraneTemplate").Copy Before:=Sheets(1)
ActiveSheet.Name = "Crane"

Sheets("BaseTemplate").Copy Before:=Sheets(1)
ActiveSheet.Name = "Base"

Dim intloop As Long, bloop As Long, cloop As Long

For Each rname In Worksheets("Base").Names
    If rname.Visible = True Then
        If rname.Visible Then
            ThisWorkbook.Names.Add Name:=ThisWorkbook.Name & Mid(rname.Name, 6, 100), RefersTo:=rname.RefersTo
        End If
    End If
Next rname

Answer 1

Your code looks correct for copying the template sheets and trying to set workbook-level named ranges. However, Excel VBA has some limitations when it comes to creating workbook-level named ranges directly. Named ranges are, by default, scoped to the sheet on which they are created.

To achieve workbook-level named ranges, you can try the following approach:

1. Create a New Workbook: Instead of copying sheets within the same workbook, create a new workbook and copy the sheets to the new workbook. This way, the named ranges will be scoped to the new workbook by default.

    Dim newWb As Workbook
    Set newWb = Workbooks.Add
    
    ' Copy sheets to the new workbook
    ThisWorkbook.Sheets("CraneTemplate").Copy Before:=newWb.Sheets(1)
    newWb.Sheets(1).Name = "Crane"
    
    ThisWorkbook.Sheets("BaseTemplate").Copy Before:=newWb.Sheets(1)
    newWb.Sheets(1).Name = "Base"

2. Update Named Ranges: After copying the sheets, update the names of the named ranges in the new workbook to remove any sheet references.

    Dim newName As String
    
    For Each rname In newWb.Names
        If rname.Visible Then
            newName = Replace(rname.Name, "BaseTemplate!", "")
            newWb.Names.Add Name:=newName, RefersTo:=rname.RefersTo
            rname.Delete
        End If
    Next rname

This way, you are effectively creating new named ranges in the new workbook without the sheet reference.

Remember that if your named ranges refer to specific ranges on a sheet, those references might need adjustment depending on your use case.

Please give this approach a try, it should help you create workbook-level named ranges when copying sheets to a new workbook.

Answer 2

I was able to solve this problem by moving my code outside of the worksheet module and onto a workbook level module. This solved the problem.