Is it possible to name a range without it being slaved to a single sheet? Like if I select “rnge1” on sheet 1, it will select range e2:e7 on sheet 1, and then I can go to sheet 2 and select “rnge1” and it will select range e2:e7 on sheet 2.
I’d like to use the named ranges I have in a single macro attached to a button. I have 1 button on each of 10 sheets. The ranges (rnge1, rnge2, rnge3) encompass e2:e7, e213:e184, e281:e284. I like using the named ranges because they automatically update when a row is added in the sheet, versus going into VBA to update it every time. I’d prefer to not have to make named ranges for each page.
When I try to remove the sheet name (=’MON (1)’!$E$2:$E$7) so that it looks like this: =$E$2:$E$7, it just reverts back to the one with the sheet name. The issue with this is that when I click the button on sheet 2, it runs the macro on sheet 1. I’ve also tried “=activesheet.range(e2:e7)”, which I found on another post, but it just causes an error.
here is a copy of the code for reference:
Sub DELETE_E()
Dim ws As Worksheet, rng As Range
Range("RNGE3").ClearContents
Set ws = ActiveSheet: Set rng = ws.Range("RNGE2")
With rng
.Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.Address & "),8)=""=Left($B"","""",FORMULATEXT(" & rng.Address & "))")
.Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.Address & "),7)=""=MISC.!"","""",FORMULATEXT(" & rng.Address & "))")
.Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.Address & "),3)=""=$B"","""",FORMULATEXT(" & rng.Address & "))")
.Formula = Evaluate("IF(LEFT(FORMULATEXT(" & rng.Address & "),7)=""=MID($B"","""",FORMULATEXT(" & rng.Address & "))")
On Error Resume Next
.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
Set ws = ActiveSheet: Set rng = ws.Range("RNGE1")
With rng
On Error Resume Next
.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
End With
End Sub
8
Having a name (“MyName”) with “Sheet1” as scope, you can use its address in the active sheet in the next way:
Dim rng as Range
Set rng = Range(Range("MyName").address)
rng.Select
It will select in the active sheet the range defined for “MyName”, even referring “Sheet1” as scope.
1
So this is going to be a bit of a dump… there are lots of ways to reference ranges in VBA, or just define ranges; e.g.:
dim currentNamedRange as range
set currentNamedRange = ThisWorkbook.Name("namedRange").RefersToRange 'uses a named range in the whole workbook and finds its location
set currentNamedRange = ThisWorkbook.Sheets(1).Range("E2:E7") 'uses a specified range on a sheet
set currentNamedRange = Range("E2:E7") 'a range with no qualifications, meaning it is the active sheet
Similar to setting up the named ranges by selecting them and labeling, you could also define each range in VBA and use other events, per sheet, to interact with the reference, e.g.:
Public currentNamedRange as range
Private Sub Worksheet_BeforeDoubleClick(Target As Range, Cancel As Boolean)
Select Case Target
Case Cells(1,"R")
Set currentNamedRange = Range("E2:E7")
Case Cells(2,"R")
Set currentNamedRange = Range("E213:E184")
Case Cells(3,"R")
Set currentNamedRange = Range("E281:E284")
Case Else
Exit Sub
End Select
End Sub
3
We can using INDIRECT to have a Range reference that is not scoped to any particular worksheet. The advantage of the approach over Range(Range("rnge1").Address) is that they will work no matter what worksheet is deleted.
In this example, I added 3 named ranges to the Workbook Names collection. They will work regardless of which worksheet they are qualified to.
Sub AddAndTestNamedRanges()
Rem Add the Named Ranges to the Workbook
Rem So they can work across multiple sheets.
With ThisWorkbook.Names
.Add Name:="rnge1", RefersToR1C1:="=INDIRECT(""E2:E7"")"
.Add Name:="rnge2", RefersToR1C1:="=INDIRECT(""E213:E184"")"
.Add Name:="rnge3", RefersToR1C1:="=INDIRECT(""E281:E284"")"
End With
Rem Test the Named Ranges
Debug.Print Sheet1.Name, Sheet1.Range("rnge1").Address(External:=True)
Debug.Print Sheet2.Name, Sheet2.Range("rnge2").Address(External:=True)
Debug.Print Sheet3.Name, Sheet3.Range("rnge3").Address(External:=True)
End Sub