Macro that creates named ranges on all sheets

R

Revenant

I'm trying to create a macro that will go through each of the 15
existing worksheets I have and create specific named ranges on each
Every sheet will have the same named ranges, so I'm trying to discove
a way to get Excel to generate a different name for each of them. A
added complexity is that each range is duplicated on each sheet, bu
may be named something different
 
J

JE McGimpsey

You're going to have to be a bit more specific - if the names are
different, a macro won't necessarily be any more efficient than doing
things by hand.

Is there an algorithm to how the ranges are named?

You *can* use the same name on each sheet if you use Worksheet level
names rather than Workbook level names - when they're used, they'll need
to be qualfied with the worksheet name.

This may give you a start:

Public Sub DoNames()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
ThisWorkbook.Names.Add _
Name:="myname" & .Index, _
RefersTo:=.Range("A1:J10")
End With
Next wsSheet
End Sub

which will name the range A1:J10 "myname1" on sheet1, "myname2" on
sheet2, etc.


Public Sub DoNames()
Dim wsSheet As Worksheet
For Each wsSheet In Worksheets
With wsSheet
.Names.Add _
Name:="myname", _
RefersTo:=.Range("A1:J10")
End With
Next wsSheet
End Sub

will create the name "myname" on each worksheet, so that in the
worksheet, you can use

=SUM(Sheet1!myname, Sheet2!myname)

to sum Sheet1!A1:J10 and Sheet2!A1:J10
 
R

Revenant

That seems to do the trick, and I can't thank you enough. It doesn'
matter to me what the names of the ranges are, I just knew that the
had to be different across each sheet (even though they exist in th
same place on each sheet), and what you've provided works perfectly
You've just saved me from the abyss, so cheers!!
 
Top