vba: Daten aus Namensbereich umkopieren

[sharky51]

Hallo Helmut,

ja genau das befürchte ich ja auch - dass die Bereiche zerschossen werden.

Ideen hätte ich wie man das handeln könnte - aber ob es einfach umzusetzen ist weiß ich nicht - und wie es ev. mit vba umsetzbar wäre erst recht nicht.

Ich versuche mal meine einige Möglichkeiten zu formulieren -
mit der Prämisse wie zuvor beschrieben - sollen die Datenbereiche im Zielblatt nach dem Umkopieren jeweils eine Zeile & eine Spalte voneinander getrennt sein.

1. Alle Daten und benannte Bereiche im Zielblatt vor einem Um-Kopiervorgang löschen - somit ist nichts mehr im Wege und die Datenbereiche können neu angelegt werden.

2. Oder - man fügt beim ersten Kopiervogang einen Zeilen- und Spaltenpuffer ein - den man ev. über eine Dialogbox festlegen kann (Muss aber nicht sein) - z.B. 5 Zeilen & 3 Spalten.
Mehr als 5 Zeilen & 3 Spalten Puffer werde ich für diesen Anwendungsfall sicher nicht benötigen. Ist der Um-Kopiervorgang beendet löscht man alle freien Zeilen/Spalten, bis auf eine Zeile/Spalte wieder weg.

3. Oder - man erkennt beim Kopiervorgang wie groß der neue Datenbereich ist und schiebt dann u.U. eine weitere Zeile/Spalte vor den nächsten Datenblock.

4. Wie unter 1. Alle Daten und benannte Bereiche im Zielblatt vor einem Um-Kopiervorgang löschen - bewerten wie groß der größte Datenbereich in den Quellblättern ist - und dann alle Datenbereiche im Zielblatt auf diese Größe einstellen.

Was noch wichtig wäre ist, wenn die Quelldaten gefiltert sich - also nicht alle Zeilen sichtbar sind - dann sollen auch nur diese ins Zielblatt kopiert werden.

Ich hänge mal ein Beispiel-Zielblatt an. So sollte die Aufteilung aussehen, wobei wie gesagt die einzelnen Blöcke unterschiedlich lang sein können.

Sicher gibt es noch elegantere Möglichkeiten so etwas abzubilden - es führen viele Wege nach Rom

Vielleicht kannst Du jetzt nochmal etwas zaubern!

[Ego]

Hallo Erich,

wenns so "gekachelt" (die ersten Zellen der benannten Bereiche stehen immer in der gleichen Zeile oder Spalte) ist, geht eine Erweiterung.
Immer wenn ein Bereich in eine andere "Kachel" reichen würde, werden entsprechend viele ganze Zeilen oder Spalten hinzugefügt.

[Ego]

Hallo Erich,

in der Anlage ein Programmversion, die die hinteren Kacheln nach unten/rechts verschiebt, wenn der Bereich ansonsten in die nächste Kachel reichen würde.

[sharky51]

Hallo helmut,

das ist genial, vielen Dank!

Ist es jetzt noch möglich nur den sichtbaren Bereich der Quell-Mappe zu übertragen? Im Moment wird ja alles was im Bereichsnamen der Quell-Mappe steht mit übertragen.
Ich blende aber mittels eines Makros alle Zeilen ohne Hintergrundfarbe aus - heißt ich will nun nur die jetzt sichtbaren Zeilen umkopieren.

Gibt es dazu noch eine Möglichkeit? Vielleicht ist die Größe des Namenbereiches mit der Filterung anzupassen eine einfache Möglichkeit.

Hier die Makros (Quelle aus dem Netz) für die Filter ein- und ausblenden:

Code:

'Blendet alle Zeilen in allen Tabellenblätter ohne Hintergrundfarbe ab Blatt 2 aus
Sub Hide_Zeilen_ohne_Farbe()
 
 On Error Resume Next
 
 'letzte Zeile in Spalte A suchen
 Dim Zeilenanzahl As Long
 Zeilenanzahl = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
 
 'letzte Spalte in Zeile 1 suchen - falls alle Spalten durchsucht werden sollen
 Dim Spaltenanzahl As Integer
 'Spaltenanzahl = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
 
 'Spalte J
 Spaltenanzahl = 11
 Startzeile = 5
 Startspalte = 8
 
 Dim i As Long, n As Integer, myColor As Boolean
 For i = Startzeile To Zeilenanzahl
   'For n = 1 To Spaltenanzahl
   Cells(i, Startspalte).Select
   'Falls Du mal alles farbige (Hintergrund) ausblenden willst
   'If Cells(i, n).Interior.ColorIndex <> xlNone Then
   If (Cells(i, Startspalte).Interior.ColorIndex = 43) Or (Cells(i, Startspalte).Interior.ColorIndex = 44) Then
     myColor = True
     'Exit For
   End If
   'Next n
   If myColor = False Then
     Rows(i).Hidden = True
   End If
   myColor = False
 Next i
End Sub


'Blendet alle ausgeblendeten Zeilen in allen Tabellenblätter ab Blatt 2 wieder ein
Sub AllesEinblenden()
 
 SheetNumber = 3       'Start ab Tabelle3
 
 With ActiveWorkbook
   ' Begin the loop.
   For i = SheetNumber To Worksheets.Count
     Worksheets(i).Select
     
     With ActiveSheet
       .Columns.Hidden = False        'Kurzform
       .Rows.EntireRow.Hidden = False 'Langform
     End With
   Next i
   
 End With
End Sub

Habe schon überlegt ob erst alle Datenblöcke komplett ins Zielblatt übertragen werden sollen und dann erst alle Zeilen ohne Hintergrundfarbe auszublenden sind.
Das funktioniert aber nicht, weil die Blöcke ja unterschiedlich lang sind - und immer in einer Zeile in irgend einer Spalte eine Zelle einen farbigen Hintergrund hat.
Ist deshalb wohl keine Option!

Wäre schön wenn Du da noch einen Tipp oder Lösung hättest.

[Ego]

Hallo Erich,

hier eine Version, in der nur die sichtbaren Zeilen kopiert werden.

ps. Da beim Anlegen eines Filters auch ein Name erzeugt wird, dürfen die zu kopierenden Bereiche jetzt im Namen die Zeichenfolge "_Filter" nicht mehr enthalten.

[sharky51]

Hallo helmut,

das ist einfach genial was Du da anbietest, super!
VIELEN DANK! - für Deine Mühe und Geduld!

Jetzt muss ich den Code nur noch komplett verstehen.

Ich wünsche Dir ein Schönes WE.

[sharky51]

Hallo helmut,

ich hätte da noch eine Frage.
Die umkopierten Datenbereiche in die Zieltabelle können, wie bereits beschrieben, unterschiedliche Längen haben.
Dadurch entstehen zwischen den Datenblöcken unterschiedlich viele leere Zeilen.
Zur Anschauung ist die Tabelle nochmals beigefügt.

Meine Frage jetzt dazu:
Wie kann man per vba alle leeren Zeilen zwischen den Datenblöcken nach dem umkopieren herauslöschen - aber wegen der Übersichtlichkeit sollen jeweils zwei Zeilen Abstand zwischen den Datenblöcken erhalten bleiben.

Vielleicht hast Du mir da noch einem Tipp?

[Ego]

Hallo Erich,

A) Bisher habe ich die Erweiterung so eingerichtet, dass nach einer Erweiterung immer mindestens eine Leerzeile/-spalte zwischen den Datenblöcken ist. Sollen das bei den Zeilen nun zwei sein?

B) Da ich nicht weis, wie die Blöcke nach einer Kürzung aussehen, dachte ich, dass du, wenn die Blöcke zu weit auseinanderstehen, einfach vor einem Kopiervorgang manuell einige Zeilen oder Spalten entfernst. Hierbei must du nicht auf die genaue Zeile/Spalte achten, da ja, wenn du zuviel löscht, die Bereiche wieder erweitert werden. Nur die Leerzeile/-spalte vor dem Block und die erste Zeile/Spalte des Blockes müssen bleiben.


C) Man könnte aber auch ein Programm schreiben, dass die Abstände der Kacheln minimiert. Es könnte über einen eigenen Button angestossen werden oder nach jedem Kopiervorgang laufen.

[sharky51]

Hallo helmut,

es wäre schön wenn die Variante "C" mit einem separaten Button möglich wäre.
Als Minimum der Abstände zwischen den Kacheln sollten aber zwei Zeilen bestehen bleiben bzw. wenn notwendig auf zwei Zeilen erweitert werden.

Ich habe mich selbst mal ein wenig versucht, aber mit weniger Erfolg.
Es werden nicht alle leere Zeilen erkannt weil die Schleifenvariable ja hochläuft.

Code:

Sub ZeileLeer()
  With ActiveSheet.UsedRange
   leZeile = .Row + .Rows.Count - 1
 End With
 For i = 2 To leZeile
       If WorksheetFunction.CountA(Rows(i)) = 0 Then
     MsgBox "Zeile " & i & " ist leer"
     Rows(i).Delete shift:=xlUp
     leZeile = i - 1
   Else
     MsgBox "Zeile " & i & " ist nicht leer"
   End If
  Next
End Sub

[Ego]

Hallo Erich,

In dem Zieltabellenblatt nach leeren Zeilen und Spalten zu suchen passt meines Erachtens nicht  zu den bisherigen Funktionen. Ich habe daher folgendes realisiert:

Das Programm sucht in jeder Kachelzeile/-spalte nach dem längsten benannten Bereich. Wenn die Kachelläng um mehr als 2Zeilen/1Spalte länger ist, werden die entsprechenden Zeilen/Spalten gelöscht.

Das heist, dass manuelle Einträge im Zielarbeitsblatt in den/der  2Zeilen/1Spalte vor einer Kachel nicht gelöscht werden, wenn sie