Hi,

I am using VBA to automatically create a named range (named PO_1) in workbook A pointing to a range of cells in workbook B, using a VBA worksheet change event which is fired when a specific cell in Workbook A / Worksheet 1 is modified. In Workbook A, this named range appears in equations in worksheet 1 and worksheet 2.

When this range is non-existent, the equations return an error message, obviously. When this range is created, and Workbook B is already open, everything is ok. However, if Workbook B is closed when the named range is created in Workbook A, equations in worksheet 2 change the named range in the equation from PO_1 to 'PO_1' and generates an error. This only happens to equations in Worksheet 2; in worksheet 1, which is the active sheet, all equations are normal.

The only workaround is to perform a search/replace to manually remove the single quotes, but aside from that are there any suggestions to ensure Excel doesn't automatically add single quotes to named ranges?

Thanks to all in advance

Joseph Guindi