vba: Daten aus Namensbereich umkopieren

[sharky51]

Hallo zusammen und guten Abend,
vielleicht könnt Ihr mir mal wieder weiterhelfen.

Ich habe eine Arbeitsmappe mit einer sich ändernden Anzahl von Arbeitsblättern.
In diesen Blättern sind verschiedene Datenbereiche . Die Bereiche sind sind jeweils mit Namen benannt. Also z.B. A10-D10 -> "Daten1" usw. Die Namen sind natürlich in jedem Blatt unterschiedlich und die Bereiche auch unterschiedlich groß, d.h. sie können variieren.
Was ich jetzt gerne umsetzen möchte ist, dass diese Datenbereiche per Namen angesprochen werden und auf ein Sammelblatt in derselben Arbeitsmappe per Makro umkopiert werden.
Und zwar wieder in einen Namensbereich der vorher festgelegt wurde und idealerweise gleich lautet wie der aus dem Quellbereich. Der Namensbereich auf dem Sammelblatt sollte aber nach dem Kopiervorgang die gleiche Größe haben wie der aus dem Quellbereich.

Dazu noch die Frage ob der Namensbereich in der Zieltabelle eine Mindestgröße, gleich groß (das weiß ich ja vorher nicht), oder auch nur eine benannte Zelle sein kann - von aus der dann der kopierte Datenbereich abgelegt wird und neu benannt wird.

Viele Fragen mit der Hoffnung auf Eure Hilfe.

[Ego]

Hallo s...,

so ganz verstanden habe ich die Anforderung noch nicht. Hier noch einige Fragen:

1. Haben die Bereiche wirklich identische Namen und wenn ja, welche Namen sind auf Ebene der Arbeitsmappe und welche für das Blatt angelegt?

2. Sollen nur die Inhalte oder auch die Formeln und Formatierungen kopiert werden?

3. Was passiert mit den schon vorhandenen Daten und Formatierungen in den benannten Bereichen des Sammelblattes, insbesondere wenn der neue Bereich kleiner ist?


4. Woher soll das Programm erkennen welche benannten Bereiche kopiert werden sollen?

[sharky51]

1. Die Bereiche müssen nicht die gleichen Namen haben. Hatte überlegt dass das irgendwie per Makro einfacher zu handeln ist.
Bereich "Daten1" auf dem Arbeitsblatt wird umkopiert auf den Bereich "Daten1" auf dem Sammelblatt (in der gleichen Arbeitsmappe).

2. Es sollen die Inhalte und Formatierungen übernommen werden. Formeln gibt es keine....vorerst!

3. Die vorhandenen Daten im Sammelblatt sollen vor dem Kopiervorgang gelöscht werden, natürlich unter Beibehaltung der Namensbereiche. Heißt der Inhalt der Namensbereiche soll gelöscht werden, die neuen Daten wieder rein kopiert werden und der Namensbereich auf die aktuelle Größe des Datenbereichs geschrumpft oder erweitert werden.

4. Per Makro sollen alle Datenbereiche der einzelnen Blätter in einer Schleife auf das Sammelblatt kopiert werden.


Ich hoffe ich konnte das jetzt besser darstellen.

[WillWissen]

Hallo Erich,

Vollzitate sind zu 99% unnötig, vor allem dann, wenn sich die Antwort auf einen unmittelbar davorstehenden Beitrag bezieht. Notwendige (Teil)Zitate können durch kopieren des entsprechenden Textbereichs in Verbindung mit dem 3. Symbol von rechts in der zweiten Symbolleiste erreicht werden.
Für normale Antworten bitte den Antworten-Button am Ende der Beiträge benutzen.

[Ego]

Hallo Erich,

in der Anlage ein Beispielmakro und hier noch zwei Bemerkungen:

1. Die Namen sind gleich und im Sammelblatt auf Workbookebene angelegt.
2. Es muss in der Datei eine Zelle mit Namen "Leer" geben. Wenn sich der Namensbereich im Sammelblatt verkleinert, werden die nicht mehr genutzten Zellen hiermit überschrieben.

Code:

Option Explicit

Private Sub cbTuwat_Click()
Dim strAddPaste As String
Dim strRngPaste As String
Dim strSplit() As String
Dim varRngCopy As Variant
Dim wsCopy As Worksheet
Dim wsPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range
Const strWsPaste As String = "Ziel"

Set wsPaste = ThisWorkbook.Worksheets(strWsPaste)

For Each wsCopy In ThisWorkbook.Worksheets
    If wsCopy.Name <> strWsPaste Then
        For Each varRngCopy In wsCopy.Names
            Set rngCopy = varRngCopy.RefersToRange
            strSplit = Split(varRngCopy.Name, "!")
            strRngPaste = strSplit(UBound(strSplit))
            Set rngPaste = ThisWorkbook.Names(strRngPaste).RefersToRange
            If rngCopy.Rows.Count <> rngPaste.Rows.Count Or rngCopy.Columns.Count <> rngPaste.Columns.Count Then
                strAddPaste = rngPaste.Cells(1, 1).Resize(rngCopy.Rows.Count, rngCopy.Columns.Count).Address
                ThisWorkbook.Names("Leer").RefersToRange.Copy rngPaste
                ThisWorkbook.Names(strRngPaste).Delete
                ThisWorkbook.Names.Add Name:=strRngPaste, RefersTo:="=" & strWsPaste & "!" & strAddPaste
            End If
            rngCopy.Copy rngPaste
        Next varRngCopy
    End If
Next wsCopy
End Sub

[sharky51]

Hallo helmut,

ich versuche mir Deinen Vorschlag morgen mal näher zu bringen.

Ich danke Dir auf jeden Fall für Deine Bemühungen!

[sharky51]

Hallo günther,

danke für den Hinweis....versuch das besser zu machen!

[sharky51]

Hallo Helmut,

herzlichen Dank für diese tolle Lösung. Echt super!
Hab mal ein wenig herumprobiert Bereiche zu verkleiner bzw. zu vergrößern - funktioniert super!
Werde dies jetzt mal auf meine Anwendung übertragen. Wenn ich dann noch ne Frage dazu habe würde ich mich gerne nochmals hier einklicken.

Dank an Dich und dieses tolle Forum!

[sharky51]

Hallo Helmut,

jetzt ist mir beim Testen doch noch etwas aufgefallen.

Meine "benamten" Datenbereiche befinden sich auf dem Zieltabellenblatt unter- und nebeneinander, mit minimal einer Zeile und einer Spalte Abstand zueinander.
Wenn sich jetzt z.B. ein Bereich aus dem Quellblatt vergrößert wird der folgende Bereich überschrieben.

Könnte man das Makro so abändern, dass abhängig der zu übertragenden Datenbereiche die folgenden Bereiche dementsprechend verschoben werden - Zeilen & Spalten.
Also wenn ein Berich um z.B. zwei Zeilen größer oder kleiner wird - soll der bisherige Abstand zum nächsten Datenblock (Zeile/Spalte) gleich groß bleiben.
Somit bliebe die Formatierung/Aussehen der Zieltabelle immer gleich und es werden auf den Zielblatt keine Daten verloren gehen.

Vielleicht hast Du da noch eine Lösung dazu.

[Ego]

Hallo Erich,

bei jeder Änderung einer Bereichsgrösse unter/hinter dem alten/neuen Bereich Zeilen/Spalten oder auch Zellen hinzuzufügen oder zu entfernen sollte kein Problem sein.
Aber damit zerschiesse ich sehr schnell den Aufbau der Bereiche im Zieltabellenblatt und Daten anderer Bereiche werden auseinandergerissen.

Vielleicht kannst du, abhängig vom Aufbau des Zieltabellenblattes, einen Algorithmus zur Erweiterung definieren.