Namemanager Bereich ändern nicht möglich

[ebhab]

Guten Morgen allerseits,
Ich habe ein Arbeitsblatt, in dem Namen für den Bereich Arbeitsmappe definiert sind.
Wenn ich das Arbeitsblatt kopiere, erhalten die Zellen mit Namen im kopierten Tabellenblatt den gleichen Namen mit Bezug Tabellenblatt.
Im Anschluss möchte ich die betroffenen Namen umbenennen und den Bezug in Arbeitsmappe ändern.
Das funktioniert bei mir aber nicht, da der Bereich immer ausgegraut und nicht änderbar ist.
Mittlerweile habe ich festgestellt, dass die Änderung des Bereichs generell nicht möglich ist.
Woran könnte das liegen?
Vielen Dank für jeden Hinweis!
Lg Michael

[maninweb]

Hallo,

prüfe mal, ob das Blatt bzw. die Blätter geschützt sind (Überprüfen / Blatt schützen)

Gruß

[ebhab]

Hallo,

danke, ich habe es überprüft. Weder das Blatt noch die Mappe sind geschützt.

[chris-ka]

Hallo Michael,

Ich habe da so eine Vermutung :)

Wähle so einen Bereichsnamen aus, indem du im Namensfeld den gewünschten Namen auswählst.
(Der Bereich wird dann markiert)
Du solltest nun einen neuen Menüpunkt haben mit dem Namen Tabellenentwurf.
Wähle diesen Menüpunkt aus und klicke im Menüband auf "In Bereich konvertieren". Bestätige den darauf folgenden Dialog mit Ja.

Alternativ könntest du auch die rechte Maustaste im Bereich nehmen und im Kontextmenü -> Tabelle-> in Bereich konvertieren auswählen.

[ebhab]

Hallo Chris,
Danke für die Info.

Ich habe das versucht, habe aber bemerkt, dass sich weder das Menu Tabellentools noch Entwurf im Menuband befinden. Das geht (nach Googlen) nur, wenn man einen Bereich als Tabelle formatiert. Die Folgen möchte ich aber in meiner komplexen Arbeitsmappe nicht herausfinden.

Ich versuche mal, das Problem vereinfacht darzustellen.
Ich habe eine Arbeitsmappe mit einer Jahrestabelle mit Namen 2022
Zelle A1 (Inhalt egal) hat den Namen sum22
Namemanager enthält folgenden Eintrag:
(1) Name(sum22) Bezug('2022'!$A$1) Bereich(Arbeitsmappe)

Aktion (neues Jahr): Kopieren Blatt 2022 in neue Tabelle, Name 2022(1) wird umbenannt in 2023
Namemanager enthält jetzt die Einträge:
(1) Name(sum22) Bezug('2022'!$A$1) Bereich(Arbeitsmappe)    UNVERÄNDERT
(2) Name(sum22) Bezug('2023'!$A$1) Bereich(2023)                 NEU (automatisch durch Kopie)

Ich brauche aber in (2) den Namen sum23 mit Bereich Arbeismappe.
- Ändert man im Namenfeld sum22 in sum23, legt der Namemanager einen weiteren Eintrag an:
  (3) Name(sum23) Bezug('2023'!$A$1) Bereich(Arbeitsmappe)   NEU
  Löscht man Eintrag (2) im Namemanager, hat man das angestrebte Ergebnis.
  Das ist aber aufwendig wenn es um viele Zeilen geht. 
 
- stattdessen aufrufen Namemanager und ändern 2023 in Arbeitsmappe geht nicht (Auswahl ausgegraut)

Fazit: ich kann mein Problem zwar aufwendig lösen, suche aber nach einer automatisierten Lösung.
Vorzugsweise wäre eine eigene Funktion im Basic, ich befasse mich gerade mit der Frage, ob der Namemanager Funktionen hat, um die Einträge zu lesen, ändern und löschen. Damit könnte ich meine personal.xlsb mit zig Funktionen einfach erweitern und das Blatt mit einem Klick anpassen.

Tut mir leid, wenn es fast ein Roman geworden ist. Die gesamte Mappe ist viel komplexer und ich habe versucht, das Problem so kurz wie möglich zu schildern.
Vielen Dank nochmal.

Gruß
Michael

[HKindler]

Hi,

wenn du die Mappe noch lange verwenden willst, lohnt sich vielleicht folgende Überlegung:

Wandle alle globale Namen (Bereich: Arbeitsmappe) in lokale Namen (Bereich: 2022) um. Nenne "sum22" nur noch "sum" (etc. pp.)
Willst du innerhalb des Arbeitsblatts auf "sum" zugreifen, ändert sich nichts. Willst du von einem anderen Blatt aus darauf zugreifen, dann geht das mit 2022!sum.
Wenn du nun das Blatt kopierst, passt alles automatisch.

Übrigens: wie hast du es überhaupt geschafft einen Namen "sum22" einzurichten? Das dürfte eigentlich nicht möglich sein, da dieser Name mit der Zelle SUM22 identisch ist und daher nicht vergeben werden kann.

EDIT:
Übrigens, wenn du viel mit Namen arbeitest, empfiehlt es sich den NameManager von JKP-Ads (https://jkp-ads.com/excel-name-manager.asp) zu verwenden. Der lässt auch die Umwandlung von globalen in lokale Namen und umgekehrt zu. Und kann noch viel viel mehr.

[chris-ka]

Hallo Michael,

sum22 ^^ das wird wohl eine xls Datei sein.

Anbei ein Ansatz für die Umwandlung auf dein Beispiel bezogen
https://youtu.be/j-1_Yod5WPI

Nicht vergessen nach dem kopieren des Blattes, das Blatt auch umzubenennen
der Code dazu 

Code:

Option Explicit

Sub Bereichsnamen()
    Dim myName As Name
    Dim strNewName As String
    For Each myName In ActiveWorkbook.Names
         If Left(myName.Name, 1) = "'" Then
            strNewName = Mid(myName.Name, InStr(1, myName.Name, "!") + 1)
            strNewName = Left(strNewName, Len(strNewName) - 2)
            strNewName = strNewName & Right(ActiveSheet.Name, 2)
            ActiveWorkbook.Names.Add Name:=strNewName, RefersTo:=myName.RefersToRange
            myName.Delete
         End If
    Next
End Sub

[ebhab]

Hi,

Zum Namen sum22: Das war nicht wirklich der im Excel verwendete Name. Ich habe mich in meinem Beispiel bemüht, kurze Namen einzusetze, um Verwirrung zu vermeiden. Das dieser Name natürlich nicht funktioniert, habe ich in diesem Moment leider übersehen. Die Tabelle ist im Übrigen sehr komplex und enthält umfangreiche Daten seit 2002. Das bedeutet, dass konzeptionelle Anpassungen sehr riskant werden können, da sie Auswirkungen auf ca. 25 Tabs und zahlreiche VBA-Funktionen haben.
Feld sum22 lautet in Wirklichkeit xx####nnn, wobei xx #### nn dynamische Zeichenfolgen sind, die auf andere Bereiche weisen, die im VBA-Coding ausgewertet werden.

Selbstverständlich werde ich mir den Namemanager jpk-ads mit großem Interesse anschauen. Nach 53 Jahren Softwareentwicklung habe ich nicht mehr die Zeit und Lust, solche komplexe Aufgaben im Ruhestand selbst zu entwickeln. Ich bin daher für jeden Tipp dankbar.

Beste Grüße

Michael

---

Hi,

sum22 habe ich in der vorigen Antwort schon aufgeklärt, der richtige Name wurde von mir selbst als übersichtlichere Verkürzung des tatsächlichen Namens verwendet ohne die Gültigkeit des Namens zu prüfen. Es ist auch in Wirklichkeit eine .xlsm.

Das Video schaue ich mir gerne an, auch deine Funktion ist mir klar und verständlich.

Trotz allem, warum der Bereich im Namemanager nicht geändert werden kann, hat sich mir noch nicht erschlossen.
Da ich mit den genannten Workarounds meine Arbeiten fortführen konnte, werde ich dennoch diesen Dialog auf erledigt setzen.

Ich danke euch allen, dass ihr eure Zeit für meine Anfrage geopfert habt.

Beste Grüße
Michael