Apply Range Names to your worksheet
June 9, 2009 • JP • 4 Comments • Rate This Article
If you have a worksheet with lots of cells used in formulas, then proceeded to name those cells, you'll need to apply those names to their dependent formulas to prettify them. Typically I just go to Insert » Name » Apply and do it that way, but I came up with this code to automate the process and thought I'd share.
I didn't test it on worksheet vs. workbook level names, it appears to just apply whatever names it can to the used range of the worksheet. And I'm fine with that.
' programmatically apply named ranges to worksheet
On Error GoTo ErrorHandler
' create an array and populate it with workbook names
Dim namesList() As String
ReDim namesList(0 To Names.Count - 1)
Dim nm As Name
Dim i As Long ' i = 0
For Each nm In Names
namesList(i) = nm.Name
i = i + 1
Next nm
' if no names can be applied, error msg will appear
ActiveSheet.UsedRange.ApplyNames namesList
MsgBox "Names were applied to worksheet " & ActiveSheet.Name
ErrorExit:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Resume ErrorExit
End Sub
(No Ratings Yet)
Loading ...What now?
Subscribe To The Blog
Email This Article
Follow Me On Twitter
My Facebook Page
Or just keep reading:
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: Open any Email Attachment, functionized
Next Post: Reminder: Advanced Excel Conference
Very clean code, but I find that even using the apply names internal feature, my copying flexibility diminishes due to the inherent removal of relative cell referencing. More of a personal preference.
Not sure what you mean, unless you mean copying the formula to another workbook. Otherwise, naming ranges and applying them to your formulas not only makes formulas easier to read, but makes them more portable within a worksheet. I can copy and paste them anywhere and not have to worry about references at all.
does this macro apply names defined on all pages of a wookbook to all of the cells of the page being acted upon?
@Steve
From Excel Help:
Since we're calling Names.Count in the procedure, we're using the Application-level Names collection, which should consist of all workbook and worksheet level names.