Reference Name Problems -

[Ed S.]

I am constructing a New Worksheet from a Worksheet that has data detail using a macro.

I tried to use a named reference (see Ctl3 when workbook is opened) as opposed to hard coded column referencing, i.e "A:A".

code before change looked like this (typical macro coding):
Sheets("SalesDetail").Select
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets(TargetWs).Select
Columns("B:B").Select
ActiveSheet.Paste

the changed code (using named reference FCOEMD) lookes like this:
Sheets(TargetWs).Range("B:B").Value _
= Sheets("SalesDetail").Range -("FCOEMD").Value

the Refers to value of ref. name FCOEMD is this:
=SalesDetail!$I:$I

I tested the code and it ran. (one line of code for 8 - seems like a good trade!)

I then simply added 10 or 12 more reference fields and ran a test (no other coding performed).

Several problems:

1. With the added reference fields, the application's performance went from a 3-5 second response to about 60 seconds.

2. Column headings remain Bold in first example, as they are bold in source detail sheet. When second (reference) method is used, font characteristics do not carry through on paste operation.


My questions are:

1) has anyone else experienced serious degradation in Excel performance when using Reference names? Is there a fix or technique to resolve the reference name problem as it relates to performance?

2) Does anyone know how to get Reference Name font charecteristics to carry through on copy Paste operation.