Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Excel 2007 Adding Template Sheets and Invalid Name Error (#NAME?)

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?
  #2   Report Post  
Junior Member
 
Posts: 1
Default

Quote:
Originally Posted by JohnWEngle View Post
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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
invalid reference error saving 2003 file in 2007 Gary C Excel Discussion (Misc queries) 0 December 8th 09 03:58 PM
Invalid web query Excel 2007 Bob Excel Discussion (Misc queries) 0 June 26th 09 03:51 PM
Adding Error Bars Excel 2007 Lars B Excel Discussion (Misc queries) 8 January 27th 09 12:55 PM
adding sheets based upon a template sheet [email protected] Excel Worksheet Functions 1 December 7th 07 06:03 PM
Error:Invalid File format,while opening an Excel Template file Saurabh Excel Programming 1 January 17th 05 08:15 AM


All times are GMT +1. The time now is 01:23 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"