Unexpected formula generation using named ranges
 

I have a macro that creates user workbooks from library worksheets that are
copied and renamed as needed to set up the file. The library worksheets have
a number of named ranges, so when I generate formulas on a summary worksheet
in this workbook to reference a particular cell, they take the general format
€œ=WorkSheetName!RangeName€�. Occasionally, I notice that the actual formula
resulting from this turns out as €œ=WorkBookName!RangeName€�. Obviously that
reference invokes a range on only one worksheet, and references to the same
range name on other worksheets show up as I would expect. Functionally it
behaves perfectly well. The only pattern I see is that it will happen only
on the first row of my summary (first instance of the specific formula), but
it will happen in the middle of a row or not at all

My typical code (tempvalue is a lookup worksheet name) -

With Range("A7")
.Offset(k, 8) = "=" & tempvalue & "!YD1"
.Offset(k, 9) = "=" & tempvalue & "!YDD"
.Offset(k, 11) = "=" & tempvalue & "!MATL"
End With

Can anyone explain why this would happen?

--
Al C

Unexpected formula generation using named ranges
 

Accidentally posted in the wrong group.
--
Al C


"Al" wrote:

I have a macro that creates user workbooks from library worksheets that are
copied and renamed as needed to set up the file. The library worksheets have
a number of named ranges, so when I generate formulas on a summary worksheet
in this workbook to reference a particular cell, they take the general format
€œ=WorkSheetName!RangeName€�. Occasionally, I notice that the actual formula
resulting from this turns out as €œ=WorkBookName!RangeName€�. Obviously that
reference invokes a range on only one worksheet, and references to the same
range name on other worksheets show up as I would expect. Functionally it
behaves perfectly well. The only pattern I see is that it will happen only
on the first row of my summary (first instance of the specific formula), but
it will happen in the middle of a row or not at all

My typical code (tempvalue is a lookup worksheet name) -

With Range("A7")
.Offset(k, 8) = "=" & tempvalue & "!YD1"
.Offset(k, 9) = "=" & tempvalue & "!YDD"
.Offset(k, 11) = "=" & tempvalue & "!MATL"
End With

Can anyone explain why this would happen?

--
Al C