Thema: Mit VBA-Routine Bereichsnamen festlegen und ändern

StandardModule: basMain

Sub AlteInNeueNamen()
   Dim nme As Name
   Dim arr() As String
   Dim iCounter As Integer, iName As Integer
   Dim sName As String, sWks As String, sAdr As String
   ReDim Preserve arr(1 To 3, 1 To ThisWorkbook.Names.Count)
   For Each nme In ThisWorkbook.Names
      sAdr = nme.RefersTo
      sWks = Left(sAdr, InStr(sAdr, "!") - 1)
      sWks = Right(sWks, Len(sWks) - 1)
      sName = Right(sAdr, Len(sAdr) - InStr(sAdr, "!"))
      iCounter = iCounter + 1
      arr(1, iCounter) = sWks
      arr(2, iCounter) = sName
      arr(3, iCounter) = nme.Name
      nme.Delete
   Next nme
   For iName = 1 To iCounter
      Worksheets(arr(1, iName)).Range(arr(2, iName)).Name = _
         "New" & arr(3, iName)
   Next iName
End Sub

Sub NamenAendern()
   Dim nme As Name
   For Each nme In ThisWorkbook.Names
      nme.Name = "New_" & nme.Name
   Next nme
End Sub

Beiträge aus dem Excel-Forum zu den Themen Allgemein und Name