how view a WS code/properties

S

Steven

I have defined some names on one of my WS's and need to make the same names
on some other sheets, how can I view my WB's names so that I can quickly
copy the names over to other WS's? everything in the definitions will be the
same bar the first letter.

Many thanks,

Steve
 
B

Bob Phillips

Steve,

Here is some starter code

Dim nme As Name
Dim sName As String
Dim sRefersTo As String

For Each nme In ActiveSheet.Names
sName = Mid(nme.Name, InStr(1, nme.Name, "!") + 1, 99)
sRefersTo = Mid(nme.RefersTo, InStr(1, nme.RefersTo, "!") + 1, 232)
Worksheets("Sheet3").Names.Add Name:=sName, RefersTo:="=Sheet3!" &
sRefersTo
Next nme

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
T

Tom Ogilvy

If you have defined the names at the workbook level, then they can not refer
to other sheets. You can have duplicate names if you make them worksheet
level

Dim nm as Name
for each nm in ActiveWorkbook
cells(rw,1).Value = nm.name
cells(rw,2).Value = nm.RefersTo
Next
 
S

Steven

Thanks for the help, could explain what that coding does for me please?

My WS with the names i want to copy is called M, will this code copy all
names on WS M to all active WS's? changing the first letter of the names to
the name of the active WS's etc?
 
S

Steven

They will not be duplicates I will edit the first letter of them to be the
same as the WS they are for.

Steve
 
B

Bob Phillips

Steven,

No, but this should

Dim sh As Worksheet
Dim nme As Name
Dim sName As String
Dim sRefersTo As String

For Each sh In Activeworkbook.Worksheets
If sh.Name <> "M" Then
For Each nme In sh.Names
sName = Mid(nme.Name, InStr(1, nme.Name, "!") + 2, 99)
sRefersTo = Mid(nme.RefersTo, InStr(1, nme.RefersTo, "!") +
1, 232)
Worksheets("Sheet3").Names.Add _
Name:=sh.Name & sName, _
RefersTo:="=Sheet3!" & sRefersTo
Next nme
End If
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Top