On May 27, 12:10*pm, Neal Zimm wrote:
Paul,
* I'm far from an MVP but am going thru kinda the
* same thing.

* Named ranges are @ workbook level, cannot duplicate them.
* I tried. *Putting in the same name again overrides the first.

* You'll need an "identification" section in your general code.

* Below has not not been tested but should be very close to working.
* The function at the end works. I use it.
* You'll be dealing with the Workbook.Names collection and the
* .RefersTo property.

*1st sheet, Ws1 Named range "RangeOne" *=Ws1!$A$1:$D$1 *4 cells row 1
*2nd sheet, Ws2 Named range "RangeTwo" *=Ws2!$E$9:$H$9 *4 cells row 9

*Sub General()

*Dim Ws as worksheet, NamedRange as string, DataRng as range
*dim CellsAdr as string

*if activesheet.name = "ws1" then
* *set ws = sheets("ws1")
* *NamedRange = "RangeOne"

*elseif activesheet.name = "ws2" then
* *set ws = sheets("ws2")
* *NamedRange = "RangeTwo"
*end if

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

*set datarng = Ws.range(cellsadr) *'4 cells worth of data from wherever

*'Your code. You'll have to get the answer back to the correct Ws.
*' *If Ws.Name = "Ws1" then .....

*End sub

*Public Function sCellAdr_vsRefToF(sRefersTo As String) As String

*'Return plain address portion from *"=Ws1!$A$1:$D$1 *.Names(xxxx).RefersTo
*'areas will work too *"=Ws1!$A$1:$D$1,!$H$8:$J$10

*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- Hide quoted text -

- Show quoted text -

Instead of that, you can just pass the current RANGE to the function/
sub as an argument.