Benannte Bereiche in Tabellenblatt-Kopien

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
InputBox MsgBox
Bild

Betrifft: Benannte Bereiche in Tabellenblatt-Kopien
von: Jens
Geschrieben am: 15.09.2015 18:24:07

Guten Abend,
ich habe ein Tabellenblatt mit benannten Bereichen.
Diese Bereiche sind bis dahin arbeitsmappenweit eindeutig und weisen im Namens-Manager in der Spalte "Bezieht sich auf" den Wert "Arbeitsmappe" auf.
Nun möchte ich das Blatt (mehrfach)
- innerhab der Arbeitsmappe kopieren
- jeweils umbenennen
- die jeweils enthaltenen Bereiche umbenennen (anderes Präfix)
Die Bereiche in den kopierten Blättern haben nun (sicherlich, weil sie unmittelbar nach dem Blatt-Kopieren ja den jeweils gleichen Namen haben, wie die schon existenten Bereiche des Quell-Blatts) im Namens-Manager in der Spalte "Bezieht sich auf" den Namen des jeweiligen (neuen) Tabellenblatts. Das ändert sich auch nach dem Umbenennen der Bereiche per Namens-Manager nicht. Nur Löschen und Neu-Anlegen ändert das wieder auf "Arbeitsmappe".
Ich möchte die Bereiche aber per VBA mit ... range("blabla") ansprechen können, ohne davor jeweils den Blattnamen angeben zu müssen.
Es ist aber mühsam(er), nach dem Blatt-Kopieren, hierfür alle jeweiligen Namen löschen und neu anlegen zu müssen, als sie einfach umzubenennen. Nur wie bringe ich Excel bei, dass sie nun doch wieder arbeitsmappenweit eindeutig sind und arbeitsmappenweit angesprochen werden dürfen?
Besten Dank, falls jemand hier weiterhelfen kann.
Grüße
Jens

Bild

Betrifft: AW: Benannte Bereiche in Tabellenblatt-Kopien
von: fcs
Geschrieben am: 16.09.2015 05:42:42
Hallo Jens,
ich persönlich würde hier bei der Verarbeitung unter VBA die Variante mit gleichen Namen in jedem Arbeitsblatt vorziehen. Durch Verwendung einer Objektvariablen, die gleich dem entsprechenden Blatt gesetzt wird, wird Code doch übersichtlich und strukturiert.
Beispiel für beide Varianten:

Sub DoSomething()
    Call aaTest(strPref:="aa")
    
    Call bbTest(strSheet:="Test 01")
End Sub
Sub aaTest(ByVal strPref As String)
    'Bereich der Namen ist Arbeitsmappe
    Dim rngNamen As Range
    Dim rngOrte As Range
    
    Set rngNamen = Application.Range(strPref & "_Namen")
    Set rngOrte = Application.Range(strPref & "_Orte")
    
    
End Sub
Sub bbTest(ByVal strSheet As String)
    'Bereich der Namen ist Tabellenblatt
    Dim wks As Worksheet
    Dim rngNamen As Range
    Dim rngOrte As Range
    
    Set wks = Worksheets(strSheet)
    
    Set rngNamen = wks.Range("Namen")
    Set rngOrte = wks.Range("Orte")
    
End Sub
Du kannst aber mal probieren, ob das nachfolgende Makro dich deinem Ziel näher bringt.
Gruß
Franz
ub BlattKopieren_Namen_neue_Prefix()
    Dim wkb As Workbook
    Dim wksAlt As Worksheet
    Dim wksNeu As Worksheet
    Dim strPrefAlt As String, strPrefNeu As String
    Dim objName As Name, objNameCopy As Name
    Dim strNameAlt As String, strNameNeu As String
    Dim strBlattNeu As String
    Dim strTitel As String
    
    Set wkb = ActiveWorkbook
    Set wksAlt = ActiveSheet
    
    strTitel = "Blatt kopieren und Namen umbenennen"
    
    strPrefAlt = InputBox("Namens-Prefix in der Quelltabelle """ _
        & wksAlt.Name & """?", strTitel, "aa_")
    If strPrefAlt = "" Then GoTo Beenden
    
BlattKopieren:
    strBlattNeu = InputBox("Blattname für kopierte Tabelle?", _
        strTitel, wksAlt.Name & " (2)")
    If strBlattNeu = "" Then GoTo Beenden
    
    If fncCheckSheet(strBlattNeu) = True Then
        MsgBox "Der eingegebene Blattname existiert bereits", _
            vbOKOnly, strTitel
        GoTo BlattKopieren
    End If
    
    strPrefNeu = InputBox("Neue Namens-Prefix in der kopierten Tabelle?", _
        strTitel, strPrefAlt)
    If strPrefNeu = "" Then GoTo Beenden
    
    If LCase(strPrefNeu) = LCase(strPrefAlt) Then
        MsgBox "Pre-Fix alt und neu müssen verschieden sein!!", _
            vbOKOnly, strTitel
        Exit Sub
    End If
    
    wksAlt.Copy after:=wksAlt
    Set wksNeu = ActiveSheet
    
    For Each objName In wkb.Names
        If Left(objName.Name, Len(strPrefAlt)) = strPrefAlt Then
            strNameAlt = objName.Name
            strNameNeu = strPrefNeu & Mid(strNameAlt, Len(strPrefAlt) + 1)
            Set objNameCopy = wkb.Names("'" & wksNeu.Name & "'!" & strNameAlt)
            wkb.Names.Add Name:=strNameNeu, RefersTo:="='" & wksNeu.Name & "'!" _
                & wksNeu.Range(strNameAlt).Address
            objNameCopy.Delete
        End If
    Next
    wksNeu.Name = strBlattNeu
    If MsgBox("Weitere Kopie erstellen?", _
        vbQuestion + vbOKCancel, strTitel) = vbOK Then GoTo BlattKopieren
        
Beenden:
End Sub
Public Function fncCheckSheet(ByVal strName, Optional wkb As Workbook) As Boolean
    'Prüft, ob Blattname in Arbeitsmappe vorhanden ist - True = vorhanden
    Dim objSheet As Object
    On Error GoTo Fehler
    If wkb Is Nothing Then Set wkb = ActiveWorkbook
    Set objSheet = wkb.Sheets(strName)
    fncCheckSheet = True
    Exit Function
Fehler:
End Function


Bild

Betrifft: AW: Benannte Bereiche in Tabellenblatt-Kopien
von: Jens
Geschrieben am: 16.09.2015 08:23:45
Hallo Franz,
ganz herzlichen Dank für Deine tolle Hilfe.
Dein Code aus dem zweiten Beispiel funktioniert super.
Aber ich werde auch Deinen Rat, es mit gleichnamigen, arbeitsblattbezogenen Bereichen zu handhaben, ernsthaft erwägen.
Beste Grüße und Dank
Jens

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Benannte Bereiche in Tabellenblatt-Kopien"