Copy of some Named Ranges to new Workbook not working

HKafeman

New Member
Joined
Jun 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I hope someone can help with this VERY strange issue?

We use the following code to copy 3 worksheets to a new spreadsheet:
<Worksheet1Name>.Copy
With ActiveWorkbook
<Worksheet2Name>.Copy .Worksheets(1)
<Worksheet3Name>.Copy .Worksheets(2)
:: :: ::

This has been working okay for a very long time. But strangely now some Named Ranges which have (only) Workbook scope, from Worksheet2 do not get copied across and then are not available to other Spreadsheets accessing the new Spreadsheet when it haas been saved.

When the Worksheet2 is selected in the new Spreadsheet, the missing Names are not shown in the Formula Bar dropdown, but are visible in Name Manager, but with the full path back to the original Spreadsheet, not just "<Worksheet2Name>!$B$42" in the "Refers To"! - "Edit Links" fails to remove the linkage when "Break Links" is tried as well!

If I delete and re-create the Named Range I still get the same problem. If I create a new Named Range with a different name, but for the same single Cell then the new names work okay!

So, can anyone shed any light on what could be happening and why or how to investigate further? Plus importantly how to fix this???

It is holding up our development of a vital Spreadsheet, which needs to be able to access the Named Range that is in existing saved Spreadsheets and to save to new Spreadsheets when it created them!

This is driving me crazy! - Why should some Named Ranges work as before and some now not be working!?!

Thanks
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

HKafeman

New Member
Joined
Jun 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Please can someone help with this?
I have found some similar posts, but with no solutions, so this is definitely an issue with Excel, not just with our Spreadsheet, etc.!!!
Hopefully one of you has an understanding of how/why this happens and how to resolve or work around???
 

HKafeman

New Member
Joined
Jun 20, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Meanwhile I have managed to sort out a workaround!

It relies on the "ValidWorkbookParameter" Property of the Named Range being False if there is a problem with it! - I spotted this to be the case for my "TroublesomeName" versus other Named Ranges that are working okay!

Does anyone know what the "ValidWorkbookParameter" Property is supposed to mean and in what circumstances it is set to True versus False? - I really would like to know why it is False now in my case when previously it was working okay!


VBA Code:
    Dim lNmCt As Long
    Dim bTroublesomeNameOk As Boolean
    Dim sTroublesomeNameRefersTo As String
    Dim sTroublesomeNameCell As String
    ::    ::    ::
    With ActiveWorkbook
        For lNmCt = .Names.Count To 1 Step -1
            If (.Names(lNmCt).Name = "TroublesomeName") Then
                If (.Names(lNmCt).ValidWorkbookParameter) Then
                    bTroublesomeNameOk = True
                Else
                    sTroublesomeNameRefersTo = .Names("TroublesomeName").RefersTo
                    'Get the actual cell reference after "!"
                    sTroublesomeNameCell = Right(sTroublesomeNameRefersTo, Len(sTroublesomeNameRefersTo) - InStr(sTroublesomeNameRefersTo, "!"))
                End If
                Exit For
            End If
        Next
    ::    ::    ::
        If (Not bTroublesomeNameOk) Then
            ' Add TroublesomeName back in so refers to new Spreadsheet and not back to the one that created it!
            .Names.Add Name:="TroublesomeName", RefersTo:=.Worksheets("sys_LinkedFilesData").Range(sTroublesomeNameCell)
        End If
    End With
 

Forum statistics

Threads
1,181,947
Messages
5,932,918
Members
436,869
Latest member
ABGTH

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top