AW: Benannte Namen automatisch auflisten
11.04.2020 20:29:54
Hajo_Zi
das Stand nicht im ersten Beitrag.
Option Explicit
'* H. Ziplies *
'* 11.04.20 *
'* erstellt von HajoZiplies@WEB.de *
Sub Namen_Auslesen()
Dim LoZeile As Long
Dim naName As Name
If ActiveWorkbook.Names.Count > 0 Then
Range("F1") = "Name"
Range("G1") = "Tabelle"
Range("H1") = "Bereich"
LoZeile = 1
For Each naName In ActiveWorkbook.Names
If UCase(naName) "=#NAME?" And InStr(UCase(naName.Name), UCase("_FilterDatabase") _
) = 0 _
And InStr(UCase(naName.Name), "#BEZUG!") = 0 And InStr(UCase(naName.Name), _
UCase("#erf")) = 0 _
And InStr(UCase(naName.Name), "PRINT_AREA") = 0 Then
If InStr(naName.Name, "!") > 0 Then
' Name
Cells(LoZeile + 1, 6) = Mid(naName.Name, InStr(naName.Name, "!") + 1)
Else
' Name
Cells(LoZeile + 1, 6) = naName.Name
End If
' Tabelle einschl Zellbereich
' Tabellenbezug bei lokalen Namen
If InStr(naName.RefersTo, "!") > 0 Then
Cells(LoZeile + 1, 7) = Mid(naName.RefersToLocal, 2, InStr(naName.RefersTo, _
"!") - 2)
Cells(LoZeile + 1, 8) = Mid(naName.RefersTo, InStr(naName.RefersTo, "!") + _
1)
Else
Cells(LoZeile + 1, 10) = naName.RefersTo
End If
LoZeile = LoZeile + 1
End If
Next naName
End If
End Sub
Gruß Hajo