Thema: Bereichsbezüge sind in Tabellenbl. unterschiedlich | Herbers Excel-Forum

Hallo alle zusammen,
ich habe für eine Arbeitsmappe Bereichsnamen vergeben, um besser rechnen zu können.
Da sich diese immer änderten (durch VBA-Löschung von Zeilen habe sie sich immer um 60 reduziert), habe ich sie über VBA nach der Zeilenlöschung neu gesetzt:
wbGesamt.Names.Add "Bereich", shGesamt.Range("F7:F1000")
Wobei wbGesamt die Arbeitsmappe und shGesamt das Tabellenblatt "Gesamt" ist und sich F7:F1000 auf dieses Tabellenblatt bezieht. Nun stelle ich erstaunt fest, dass der Bereich im anderen Tabellenblatt nur von F7:F460 angezeigt wird, wenn ich mir diesen über Einfügen/Name/Definieren anzeigen lasse (das war das Problem, warum ich mit VBA den Bereich neu gesetzt habe).
Ich dachte eigentlich immer: Bereich ist Bereich und ist für alle Tabellenblätter gleich, da es sich auf den NAMEN bezieht. Muss ich diesen nun für jedes Tabellenblatt neu definieren? Bin echt sprach- und ratlos.
Vielen Dank im voraus für die Hilfe
sunni

Hallo sunni,
die Namens-Definition mußt Du so machen, da der 2. Parameter (RefersTo) vom Typ 'Text' ist und kein Range-Bereich.
wbGesamt.Names.Add "Bereich", "=" & shGesamt.Name & "!F7:F1000"
Gruß von Luschi
aus klein-Paris

danke für deine Hilfe.
Funzt leider nicht so wirklich:
in dem 2. Tabellenblatt wird immer noch um 60 reduziert. und jetzt wird durch deine Formel im 1. Tabellenblatt immer hochgezählt:
Beim ersten Lauf von F7:F1000 auf F10:F1003 und beim 2. Lauf auf F13:F1006.
Es wird also immer um 3 Zeilen verschoben (?)).
Noch andere Ideen?

Hallo Sunni,
Du definierst den Namen "Bereich" nur für das Blatt "Gesamt" (bzw. Objektvariable shGesamt).
Durch das Löschen der Zeilen und das Neudefinieren des Namens wird dieser daher nur für dieses Blatt gesetzt. der Name ist hier GLOBAL, d.h. für alle Tabellenblätter bezieht er sich auf shGesamt !
Ich hätte folgenden Lösungsvorschlag : Name nicht global per absoluten Bezug definieren, sondern per INDIREKT(), damit gilt er jeweils für das aktuelle Blatt.
Beispiel : Test1 + NamenDefinieren1 sind wie in Deinem Beispiel, Test2 + NamenDefinieren2 wie in meinem angedachten Beispiel. Teste die Makros mal in einer neuen Mappe, der jeweilige Bereich wird zur optischen Erkennung jeweils gelb eingefärbt und mit "Test" befüllt :

Option Explicit
Sub Test1()
NamenDefinieren1
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
Rows("100:159").Delete shift:=xlUp '60 Zeilen löschen
Stop 'STOP zur Kontrolle - Fortfahren mit Taste F5 im Code !
NamenDefinieren1 'Namen neu definieren
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
End Sub
Sub Test2()
NamenDefinieren2
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
Rows("100:159").Delete shift:=xlUp '60 Zeilen löschen
Stop 'STOP zur Kontrolle - Fortfahren mit Taste F5 im Code !
'NamenDefinieren2 - muss hier nicht neu definiert werden !
With Range("Bereich")
.Interior.Color = vbYellow 'Bereich gelb einfärben
.Value = "Test"
End With
End Sub
Sub NamenDefinieren1()
Dim wbGesamt As Workbook, shGesamt As Worksheet
Set wbGesamt = ActiveWorkbook
Set shGesamt = Worksheets("Gesamt")
'Der Name wird hier nur für das Blatt shGesamt definiert :
wbGesamt.Names.Add "Bereich", shGesamt.Range("F7:F1000")
End Sub
Sub NamenDefinieren2()
Dim wbGesamt As Workbook, shGesamt As Worksheet
Set wbGesamt = ActiveWorkbook
Set shGesamt = Worksheets("Gesamt")
'Der Name wird hier universell für jedes Blatt auf Bereich F7:F1000 definiert !
wbGesamt.Names.Add "Bereich", "=indirect(""F7:F1000"")"
End Sub

Gruß, NoNet

danke für deine Mühe, jetzt hab ich auch verstanden, worums geht :-)).
Leider klappts nur in Gesamt (hier steht jetzt auch "Bezieht sich auf: INDIREKT...). Hier ist mir aufgefallen, dass über Einfügen/Name/Definieren der Name des Bereiches zu sehen ist und daneben der Name des Tabellenblattes.
Komischerweise wird INDIRECT in Tabelle 2 nur bei einigen Bereichsnamen(?) übernommen. Gerade bei denen, bei denen der Name des Tabellenblattes nicht daneben stand (siehe oben).
Es sind für "Gesamt" 7 Bereiche definiert. Komischerweise hält er den Bereich (INDIREKT 7:1000) nur bei A und C. Die anderen Bereiche haben nach wie vor: "Bezieht sich auf: GESAMT 7:880 (da er schon wieder runtergezählt hat). Oder muss ich vorher noch einmal in Tabelle 2 die Bereiche neu definieren? Eigentlich doch nicht, da er sie eh neu setzt, oder?
wbGesamt.Names.Add "A", "=indirect(""R7:R1000"")"
wbGesamt.Names.Add "B", "=indirect(""T7:T1000"")"
wbGesamt.Names.Add "C", "=indirect(""S7:S1000"")"
wbGesamt.Names.Add "D", "=indirect(""F7:F1000"")"
wbGesamt.Names.Add "E", "=indirect(""D7:D1000"")"
wbGesamt.Names.Add "F", "=indirect(""E7:E1000"")"
wbGesamt.Names.Add "G", "=indirect(""Q7:Q1000"")"
Wo liegt noch der Fehler? Kann es mit dem Namen zusammenhängen?

ich habe die Bereiche in Tabelle 2 jetzt einfach gelöscht (sind ja in Tabelle 1 vorhanden) und schaue, was weiter passiert.
vielen Dank euch nochmal für die detaillierte Erläuterung und die Beispiele.
sunni