Paul,
I'm far from an MVP but am going thru kind of the same thing.
Named ranges are at workbook level, You cannot assign exactly the
same name on two diff sheets. I tried. The second time you put the same
name in, it overrides the sheet name and cell addresses of the 1st.

You'll need an intermediate step(s) to give you equivalent ranges from two
different places. I've attached a function I use that you can modify to
your needs. You'll have to deal with the Workbook.Names VBA collection and
the
.RefersTo property.

'sheet Ws1 Named Range "RangeOne" =Ws1!$A$1:$D$1 '4 cells row 1

'sheet Ws2 Named Range "RangeTwo" =Ws2!$D$9:$G$9 '4 cells row 9

Sub GeneralCode()

dim Ws As Worksheet
Dim DataRng as Range, CellsAdr as String, NamedRange as string


If activesheet.name = "ws1" then
NamedRange = "RangeOne"
Set Ws = sheets("ws1")
elseif activesheet.name = "ws2" then
NamedRange = "RangeTwo"
Set Ws = sheets("ws2")
end if


CellsAdr = _
sCellAdr_vsRefToF(Activeworkbook.names(NamedRange) .RefersTo)
' Above gets the "pure" address of the data without the sheet

Set DataRng = Ws.Range(CellsAdr) ' 4 cells of data in the range

' Your code. Above code not tested, the function below works.

End Sub


Public Function sCellAdr_vsRefToF(sRefersTo As String) As String
' Return plain address portion from "=Ws1!$A$1:$D$1
' .Names(xxxx).RefersTo
'also works for named range areas "=SheetName!$A$1:$D$1,!$R4:$S$20"

Dim Text As String, iByte As Integer

iByte = InStr(sRefersTo, "!")

Text = Right(sRefersTo, Len(sRefersTo) - iByte)

Text = Replace(Text, "!", "")

sCellAdr_vsRefToF = Text

End Function


--
Neal Z


"Paul" wrote:

I didn't mean to include the opening sentence in my original post - here is
the correct wording for my question. (sorry for any confusion):

I'm building an application that has multiple worksheets in a workbook, and
I have written some VBA code in a general module that I would like to be
able to use in each of the worksheets.

Part of the code performs actions based on the Range names of certain cells
in the worksheet. Since I would like to be able to use this same code to
perform the same operations in the various worksheets, I would like to be
able to create the same Range name to corresponding cells in each workbook,
so the code will perform the same actions in each of the worksheets. (I
need to assign Range names because the "corresponding" cells aren't always
in the same Row).

However, I have noticed that if I try to use a Range name in one worksheet
that has already been defined in another worksheet, it won't let you do it.

Is there any way I can give cells in two different worksheets the same Range
name, so the same VBA code can be used to perform the same operations in the
different worksheets?

Thanks in advance,

Paul