Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
I'm writing VBA code that performs actions based on the names of cells in a
worksheet. 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 |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
If the names are same then how will 'Excel' know which name you ant to refer
to? One simple workaround is to prefix the names by sheetnames and use that in your code. "Paul" wrote: I'm writing VBA code that performs actions based on the names of cells in a worksheet. 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 |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
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 |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workboo
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 |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workboo
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 |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
Hi Paul,
You can do this using Names that are scoped as Local to the worksheet: Define names like Sheet1!Fred as =Sheet1!$A$4 and Sheet2!Fred as =Sheet2!$B$77 etc If you download Name manager from http://www.decisionmodels.com/downloads.htm it can simplify creating and managing these kind of names. BTW although it is possible for a name to be both Local and Global I do not recommend this as it is error-prone. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Paul" wrote in message ... I'm writing VBA code that performs actions based on the names of cells in a worksheet. 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 |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workboo
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. |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
range name the cells in your worksheet, then copy the sheet
"Paul" wrote in message ... I'm writing VBA code that performs actions based on the names of cells in a worksheet. 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 |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workboo
Thanks , everyone. You've given me some interesting ideas I can experiment
with. In this particular case, Patrick Molloy's solution works just fine - range name the cell and copy the worksheet. I've tested it, and it does exactly what I require. That is, the code in the general module operates on the range in the active worksheet, and leaves the others alone, even though those other worksheets have the same range names. Paul |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
using the same range name on different worksheets in a workbook
Works great, Patrick. Please see my comments to the group elsewhere in this
thread. Thanks Paul |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine worksheets in multiple workbook in one workbook with a macro | Excel Discussion (Misc queries) | |||
Named Range From One Workbook Used in Validation Drop Down in 2nd Workbook | Excel Discussion (Misc queries) | |||
Create New Workbook from Worksheets in One Workbook | Excel Discussion (Misc queries) | |||
Sum same cell/range of multiple worksheets within a workbook... | Excel Worksheet Functions | |||
How do I build a workbook from the worksheets another workbook? | Excel Discussion (Misc queries) |