Benannte Bereiche in Tabellenblatt-Kopien

Forumthread: Benannte Bereiche in Tabellenblatt-Kopien

15.09.2015 18:24:07

Jens

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

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Benannte Bereiche in Tabellenblatt-Kopien

16.09.2015 05:42:42

fcs
AW: Benannte Bereiche in Tabellenblatt-Kopien

16.09.2015 08:23:45

Jens

AW: Benannte Bereiche in Tabellenblatt-Kopien

16.09.2015 05:42:42

fcs

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

AW: Benannte Bereiche in Tabellenblatt-Kopien

16.09.2015 08:23:45

Jens

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

Beliebteste Forumthreads (12 Monate)

Benannte Bereiche in Tabellenblatt-Kopien

Forumthread: Benannte Bereiche in Tabellenblatt-Kopien

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread