A named range is a reference that you can call up from anywhere in the
workbook, so if you name it XYZ in Sheet1 and again in Sheet2, the
Sheet1 reference point is lost.

Since you're doing this in a macro, you can make the reference name
unique by concatenating the tab name into the named range. In the
example above, you can call your range XYZSheet1 on sheet1, XYZSheet2
on sheet2, etc. However, your tab names will need to follow naming
rules for ranges: no blanks, no special characters, etc.

If tab names won't do it for you, you could concatenate other data
found on the sheet (as long as it is unique and follows naming rules),
a month name, a timestamp, whatever makes sense for your application.
You might use the INPUTBOX function of VBA to customize an entry.

Allow me to suggest: don't allow a named range to be confused with a
cell reference. For instance if your named range is "GM", don't use
"GM1" as a named range because Excel cannot distinguish between this
named range and cell GM1. It can be done but creates havoc later on.