repeat for each named range

[VegasNath]

How can I adapt the following code to repeat for each named range "essbase" within the active workbook? The named range will be defined on some but not all worksheets in the workbook, preceded by the sheet name.

Code: Select all

Sub ESSBASE_RETRIEVE()

Application.Goto Reference:=Range("essbase")
Application.Run Macro:="EssMenuRetrieve"

End Sub

[HansV]

Try this:

Code: Select all

Dim nme As Name
For Each nme In ActiveWorkbook.Names
  If LCase(Right(nme.Name, 7)) = "essbase" Then
    Application.Goto Reference:=Range(nme.Name)
    Application.Run Macro:="EssMenuRetrieve"
  End If
Next nme

[VegasNath]

Cheers Hans. Works great!

Is there an easy way to define a named range accross several sheets?

[HansV]

Yes, but what would you do with it?

[VegasNath]

update it.

I need the same defined name accross several worksheets. The only way i know how is to type the name into the box, preceding it with the sheet name, which is a pain.

[HansV]

A named range across multiple sheets is not the same as multiple named ranges, each on one sheet. You will have to update the names one by one. Perhaps you can write a macro for this purpose.

[Jan Karel Pieterse]

You can download my Name Manager. It has a list option which lists all named ranges on a worksheet. You can then edit the named ranges on the sheet and afterwards have Name Manager pick up the changes.

[VegasNath]

Thankyou, that is very useful.