[SOLVED] Is it possible to have a named range attached to "activesheet," rather than a specific sheet?

Is it possible to have a named range attached to "activesheet," rather than a specific sheet?

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

Solution

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.