Zelle mit Text innerhalb eines VBA-Bereiches ermitteln

[hellidor]

Hallo Werte Excel-Freunde,

Folgendes Problem beschäftigt mich schon eine Zeitlang und ich habe bisher nicht die passende Lösung gefunden.

Über ein Makro wird mir ein dynamischer Tabellenbereich in Tabelle1 als Range-Objekt deklariert. Diese besteht aus 7 Spalten (A-G) und die Zeilenzahl variiert.
Der Bereich soll über ein Button in ein anderes Tabellenblatt kopiert werden, welches ich im Anschluss drucken will. Dabei können mehrere dieser Tabellen in die zu druckende Tabelle2 enthalten sein. Die Blattvorlage enthält eine Kopfzeile mit einem Bild, das in die ersten 3 Zeilen eines jeden Blattes hineinreicht. Nun will ich verhindern das die eingefügte Tabelle in die ersten drei Zeilen der 3. Seite hineingeschrieben wird. Die erste Tabelle wird ab Zeile 90 hineinkopiert. Zwischen den Tabellen gibt es einen Abstand von 2 freien Zeilen

Meine Überlegung:

Einen neuen Bereich (Im konkreten Fall Zeile 133 - 135, Spalten A - G) zu definieren (Das ist der Bereich, der auf der 3. Seite frei bleiben muss).
Nun würde ich hier mit einer Intersect-Application überprüfen wollen, ob irgendein Teilbereich des zuvor ermittelten Tabellenbereiches beim kopieren mit dem freibleibenden Bereich eine Schnittmenge hat, und wenn ja, das die zu kopierende Tabelle erst ab Zeile 136 eingefügt wird.
Leider weiß ich nicht wirklich wie ich das in Programmcode umsetzen kann und ob meine Überlegung tatsächlich so funktioniert. Falls Jemanden ein anderer Lösungsweg einfällt, Bitte Gern.

P.S: Eine Möglichkeit ist mir noch in den Sinn gekommen. In der letzten Zeile der durch das Makro ermittelte Zellbereich in Spalte 2 (B) steht immer das Wort "Summe". Wenn ich diese Zelle ermittle und dann abgleiche, ob diese innerhalb der zeilen 133-135 (Spalte B) kopiert werden würde, könnte man auch sagen, das die zu kopierende Tabelle erst ab Zeile 136 eingefügt wird? Falls meine Ausführung zu unverständlich ist, bitte Bescheid geben.

[IchBinIch]

Hi,

so ganz klar ist mir Dein Unterfangen nicht .

Du willst aus verschiedenen Blättern Daten aus den Spalten A bis G in ein neues Blatt, nennen wir es mal Druckvorlage (=Tabelle2), einfügen.
Dort gibt es in der Kopfzeile eine Grafik, die verhindert, dass die ersten 3 Zeilen eines jeden gedruckten Blattes genutzt werden können.
So weit richtig?

In Excel 2007 kannst Du über Seitenlayout --> Drucktitel: "Wiederholungszeilen oben" einstellen. Ich bin mir nicht sicher, ob das in der 2013-Version
auch so ist.


   

Vorausgesetzt ich habe Dich richtig verstanden, sollte Dir das vielleicht helfen?!

Gruß
Ich

[hellidor]

Ok, ich versuche mich etwas klarer auszudrücken.

Du willst aus verschiedenen Blättern Daten aus den Spalten A bis G in ein neues Blatt, nennen wir es mal Druckvorlage (=Tabelle2), einfügen.

Genau. Ich habe mehrere Tabellen die ich in ein Druckblatt einfüge. Nun kann es ja passieren, das ein Teil der kopierten Tabelle auf diesen ersten 3 Zeilen der neuen Seite geschrieben werden. Damit hängt der Text nicht nur in dem Bild, sondern wird auch geteilt (1 Teil der Tabelle auf Seite 2, Ein Teil auf Seite 3). Ich wollte mit meiner Überlegung beides verhindern, dass also beim Einfügen einer beliebigen Tabelle diese 3 Zeilen frei bleiben, und wenn ein Teil der Tabelle in diese drei Zeilen reichen würde diese komplett auf die nächste Seite kopiert wird (Sieht aus meiner Sicht optisch besser aus wenn das alles bissel zusammenhängend bleibt).

In Excel 2007 kannst Du über Seitenlayout --> Drucktitel: "Wiederholungszeilen oben" einstellen. Ich bin mir nicht sicher, ob das in der 2013-Version
auch so ist.

Ja, diese Möglichkeit gibt es auch im XL2013.
Das habe ich getestet und finde diesen Vorschlag an sich sehr gut. Zwar wird manchmal eine Tabelle auf zwei Blattseiten verteilt, allerdings spar ich mir den Programmieraufwand den Kopfbereich frei zulassen. Für mich ein guter Kompromis.

Falls dir zu dieser "Tabellenteilungs"-Geschichte   :19:   noch was einfällt, wäre das echt der Hammer. Mit dem Drucktitel kann ich aber auch Leben und es erspart mir jede Menge Zeit.

[IchBinIch]

Hi,

teste mal so.

Option Explicit

Sub SeitenumbrücheErmitteln()
Dim z As Long, zf As Long, zf2 As Long
Dim lz As Long
Dim su1 As Integer, su2 As Integer
    
With Tabelle2
    su1 = .HPageBreaks.Count + 1                           'Ermittlung der Seitenümbrüche vor dem Einfügen neuer Daten 
    zf = .Cells(Rows.Count, 1).End(xlUp).Row + 1           'Ermittlung der ersten freie Zeile der Zieltabelle 

    lz = Tabelle1.Cells(Rows.Count, 7).End(xlUp).Row       'Ermittlung der letzten verwendeten Zeile in Tabelle1 Spalte G 
    Tabelle1.Range("A5:G" & lz).Copy .Cells(zf, 1)         'Kopieren des Bereichs ohne Überschriften in Tabelle2 erste freie Zeile 
    
    su2 = .HPageBreaks.Count + 1                           'Ermittlung der Seitenümbrüche nach dem Kopieren 
    
    If su2 <> su1 Then                                     'Wenn die Anzahl der Seitenumbrüche "vorher/nachher" unterschiedlich ist 
        zf2 = .Cells(Rows.Count, 1).End(xlUp).Row          'Ermittlung der letzten verwendeten Zelle in Tabelle2 
        For z = zf2 To zf Step -1                          'Aufsetzen der Schleife zum Löschen 
            .Rows(z).Delete                                'Alle eingefügten Zeilen löschen 
        Next z
    .Rows(.Cells(zf, 1).Row).PageBreak = xlPageBreakManual 'Manuell einen Seitenumbruch unterhalb einfügen 
    Tabelle1.Range("A5:G" & lz).Copy .Cells(zf, 1)         'Anschließend zu kopierende Daten wieder einfügen 
    End If
    
End With

End Sub

VBA/HTML-CodeConverter, AddIn für Office 2002-2016 - in VBA geschrieben von Lukas Mosimann. Projektbetreuung:RMH Software & Media
Code erstellt und getestet in Office 12 - mit VBAHTML 12.6.0

Gruß
Ich

Mein Testszenario ebenfalls angefügt