XL97: Named Range maintenance sub won't work?!?! 2

[VBAjedi]

Hi All!

I'm writing a sub to restore the named ranges in a workbook back to what I originally intended them to be (to compensate for users deleting data rows, etc). My basic approach is just to use the Worksheet.Names.Add method. If the name already exists (it should), Excel just seems to overwrite the current reference with the correct reference. This is good.

The problem is that when I run it, strange things happen. For a while it was giving me weird errors, but I added a few lines at the beginning to select cell "A1" of each sheet (to avoid relative reference issues), and that seemed to fix that. Now it creates the Named ranges, but they arent visible to the formulas on my worksheet, or to the named range drop-down box in the menu bar. They ARE visible and correctly set if I go to Insert>Name>Define. What am I doing wrong? Here's the code:

Sub RefreshNamedRanges()
' Call this sub anytime you want to refresh the ranges
Dim TargetWorkbook As Workbook, mSheet As Worksheet, mName As Name
Dim x

Set TargetWorkbook = ActiveWorkbook

For Each mSheet In TargetWorkbook.Worksheets
   x = mSheet.Name
   mSheet.Activate
   Range("A1").Select ' avoid relative range issues
Next mSheet
AssignSheets ' creates Sheet variables named shSS, shDa, and ShLB that refer to the three sheets with named ranges

TargetWorkbook.Names.Add Name:="DAData", RefersTo:="=" & ShDa.Name & "!$A$1:$U$50000"
TargetWorkbook.Names.Add Name:="LBCanLatestBal", RefersTo:="=" & ShLB.Name & "!$F$7"
TargetWorkbook.Names.Add Name:="LBUSLatestBal", RefersTo:="=" & ShLB.Name & "!$A$7"
TargetWorkbook.Names.Add Name:="SSAgingBuckets", RefersTo:="=" & ShSS.Name & "!$L$13:$L$50000"
TargetWorkbook.Names.Add Name:="SSAgingDate", RefersTo:="=" & ShSS.Name & "!$K$1"
TargetWorkbook.Names.Add Name:="SSAllData", RefersTo:="=" & ShSS.Name & "!$A$13:$AN$50000"
TargetWorkbook.Names.Add Name:="SSCurDocAmts", RefersTo:="=" & ShSS.Name & "!$N$13:$N$50000"
TargetWorkbook.Names.Add Name:="SSDataAndHeaders", RefersTo:="=" & ShSS.Name & "!$A$12:$AN$50000"
TargetWorkbook.Names.Add Name:="SSDataDate", RefersTo:="=" & ShSS.Name & "!$I$1"
TargetWorkbook.Names.Add Name:="SSDocDates", RefersTo:="=" & ShSS.Name & "!$K$13:$K$50000"
TargetWorkbook.Names.Add Name:="SSDocNums", RefersTo:="=" & ShSS.Name & "!$I$13:$I$50000"
TargetWorkbook.Names.Add Name:="SSLastWkData", RefersTo:="=" & ShSS.Name & "!$R$13:$W$50000"
TargetWorkbook.Names.Add Name:="SSOpenCRAmts", RefersTo:="=" & ShSS.Name & "!$O$13:$O$50000"
TargetWorkbook.Names.Add Name:="SSOrigDocAmts", RefersTo:="=" & ShSS.Name & "!$M$13:$M$50000"
TargetWorkbook.Names.Add Name:="SSThisWkData", RefersTo:="=" & ShSS.Name & "!$L$13:$Q$50000"
TargetWorkbook.Names.Add Name:="SSUpdatedAmts", RefersTo:="=" & ShSS.Name & "!$AD$13:$AD$50000"
TargetWorkbook.Names.Add Name:="SSUpdateDate", RefersTo:="=" & ShSS.Name & "!$AD$10"
TargetWorkbook.Names.Add Name:="SSWkBeforeData", RefersTo:="=" & ShSS.Name & "!$X$13:$AC$50000"

End Sub

Thanks for your help!

VBAjedi

 

[Zathras]

I can't reproduce your problem. The sub seems to work just fine. Names show up in the toolbar drop-down box.

There may be a problem with the over-all approach, however. If the user zaps the worksheet by deleting certain rows or columns that result in #REF! showing up in a formula in a cell, the macro won't heal that. You probably need to re-write all of your formulas after resetting the range names.

As long as you use range names in the formulas you should be ok (unless the user zaps one to #REF! per previous paragraph). But if your formulas refer to rows and columns, then the references will change as rows are deleted/inserted and re-setting range names would have no effect. That's just the way it works.

 

[VBAjedi]

I've exclusively used range names in my formulas for that very reason. And the #Ref issue I'll just have to live with.

But I've GOT to figure out this error. My code runs fine through a handful of the range update lines (not always the same number, though) before hanging on one that seems no different than the rest. Now it's back to the original behavior: runs through some, then triggers Excel's "File not found" dialog box. What is starting a file search, anyway? It's like it's trying to set a reference to a range in another workbook!


VBAjedi

 

[SkipVought]

Hey VBA,

I am assuming that each of your tables is ISOLATED from any other table so that is you were to apply the CurrentRegion method from anywhere in any table, only and all of that table would be selected.

If that's the case, I would suggest a much simpler approch based on the pre-existance of each table.

1. Loop thru all named ranges in the active workbook

2. only select named ranges in your list of Select Case

3. use a derived sheet.

Here's what it might look like...

Sub RenameTables()
    Dim n As Name, sName As String, sSheet As String
    For Each n In ActiveWorkbook.Names
        sName = n.Name
        Select Case sName
            Case "MyNames", "MyAddresses" ' these are my two tables -- list your here
                sSheet = n.RefersTo
                sSheet = Mid(sSheet, 2, InStr(sSheet, "!") - 2)
            ActiveWorkbook.Names.Add _
                Name:=sSheet, _
                RefersTo:="=" & sSheet & "!" & Range(sName).CurrentRegion.Address
        End Select
    Next
End Sub

You could also run this, or any other renaming routine in the Workbook_SheetChange event. But for that approch I prefer...

One table per sheet -- therefore only one specific rename at a time. Here's that code...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    With Sh
        ActiveWorkbook.Names.Add _
            Name:=.Name, _
            RefersTo:="='" & .Name & "'!" & .Cells(1, 1).CurrentRegion.Address
    End With
End Sub

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[VBAjedi]

Skip,

Unfortunately the ranges I am working with are NOT always isolated from each other.

Ok, I think I am close here. For some reason, on some of the named ranges my code is attempting to create references to other workbooks. Excel apparently takes the workSHEET name for a workBOOK name. . .

Insert>Name>Define shows that some of the named ranges my code created have the worksheet name surrounded by single quotes ('My Worksheet') while some do not. This, I believe, is because some of the sheet names have spaces in them. Could this possibly be confusing Excel somehow? It seems unlikely.

What am I doing that is causing Excel to try to create an external reference?

VBAjedi

 

[VBAjedi]

Got it! The Help File snippet I modeled my code after was adding a range to a sheet whose name did not have spaces. For the sake of being as confusing as possible, Microsoft left out the (apparently optional) single-quotes around the sheet name.

Once I rewrote my code to add the single-quotes, all was well.

Zathras, that's why you couldn't reproduce my problem - your sheet names were probably the default names and didn't have any spaces in them. A star for pointing out the #REF issue, though.

Skip, thanks for the alternate approach code. A star for that!

VBAjedi