Quote:
Originally Posted by JohnWEngle
I have a workbook (.xlsm) that gets built by adding in template sheets
(.xltx). There are numerous template sheets that can be added depending on
the needs of the workbook. Each template sheet refers to values found on the
the Master sheet by named ranges. The values on the Master sheet are unique
by workbook, therefore the template sheets cannot be linked to the Master
template sheet. The formulas on the template sheets use workbook level named
ranges to refer to the values on the Master sheet. The ranges on the Master
sheet are defined as workbook level named ranges. When I do a Sheet.Add
Type:=<path the template sheet is added to the workbook, however, all the
named ranges that refer to the Master sheet show an Invalid Name Error
(#NAME?). If I click on a cell with an Invalid Name error and then click in
the formula bar and then click on any cell in the workbook, Excel resolves
the invalid name. Nothing is changed, just 3 mouse clicks.
Any idea why Excel can't resolve the names when the sheet is added? |
I found a work around for this incredibly annoying problem. If you create a macro that iterates through all of the cells using the TextToColumns command all the cells are refreshed. It will crash on merged cells though. Here's the code snippit:
Dim c As string
Dim d
c = "worksheet name"
For Each d In Worksheets(b).Range("D3:K3,I4").Cells
d.Select
Selection.TextToColumns Destination:=d, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Next