List Names on sheet2 ,but only names used in sheet1

[2Raw]

ListNames creates a list of names in the entire workbook. I need to list the names used in sheet1 only. I need this list of names used on sheet1 to be created on sheet2.

Currently I am doing a ListNames on sheet1, then deleting all that end in "!REF!" because they are from other sheets then trying to copy the range of names to sheet2.

I am also having trouble copying the range.

Worksheets(Sheet1).Range("b4:e" & v).Select 'Copy old list
Selection.Cut
Worksheets(sheet2).Range("f4:i" & v).Select
ActiveSheet.Paste

That doesn't work!

I would be greatful to find out how to do both, create sheet1 names on sheet2, and copy a range from sheet1 to sheet2.

[michelxld]

Hello ,

this example list the names of the first sheet in the second sheet


Sub listerNomsPremiereFeuille()
Dim N As Name
Dim PlageNom As Range
Dim Ligne As Byte

Ligne = 1
On Error Resume Next

For Each N In Worksheets(1).Parent.Names
Set PlageNom = Nothing
Set PlageNom = N.RefersToRange

If Not PlageNom Is Nothing Then
If Worksheets(1).Index = PlageNom.Worksheet.Index Then
Worksheets(2).Cells(Ligne, 1) = N.Name
Worksheets(2).Hyperlinks.Add Anchor:=Worksheets(2).Cells(Ligne, 1), _
Address:="", SubAddress:=N.RefersToRange.Address(external:=True)
Ligne = Ligne + 1
End If
End If
Next N

End Sub



Regards ,
michel