You can see the issue with the following code:
var workbook = new Workbook();
var worksheet = workbook.Worksheets.Add("Q're");
var index = workbook.Worksheets.Names.Add("Test");
var name = workbook.Worksheets.Names[index];
var range = worksheet.Cells.CreateRange("A1:X1");
name.RefersTo = range.RefersTo;
Refersto is “=‘Q’re’!$A$1:$X$1” and should be “=‘Q’‘re’!$A$1:$X$1”
Notice the escaped apostrophe.
oQu3Yk4mGC.png (17.7 KB)
VBA code:
Dim wb As Workbook
Dim ws As Worksheet
Dim n As Name
Dim refersTo As String
Dim r As Range
Set wb = ActiveWorkbook
Set ws = wb.Worksheets.Add()
ws.Name = "Q're"
Set n = wb.Names.Add("Test", "'Q''!A1")
Debug.Print n.refersTo
8Pb2qouhBv.png (7.0 KB)