VBA: Bereich mit Variable adressieren und erweitern

[VodeAn]

Hi Leute,

ich möchte in einem Excel-Formular einen Bereich (Daten UND Formatierung) aus einem Blatt kopieren und an einer bestimmten Stelle eines anderen Blattes wieder einfügen, ausgelöst durch den Druck auf einen Button. Dafür habe ich den Code bereits und er funktioniert auch:

Code:

Worksheets("Tabelle3").Range("A1:D4").Copy _
    destination:=Worksheets("Tabelle2").Range("E5")

Jetzt aber mein Problem: So wie der Code jetzt ist, würde er bei erneutem Druck auf den Button nur den eben schon eingefügten Bereich überschreiben. Mein Ziel ist es aber, dass bei jedem Druck auf den Button der Bereich unter den schon eingefügten angehängt wird. Ich schildere mal kurz den gewünschten Ablauf:

1. Klick auf den Button
2. Der Bereich A1: D4 von Blatt 3 wird kopiert und an einer vorher definierten Stelle auf Blatt 2 eingefügt (in diesem Beispiel ab E5).
3. Erneuter Klick auf den Button
4. Der Bereich A1: D4 von Blatt 3 wird erneut kopiert, diesmal aber 5 oder 6 Zeilen weiter unten auf Blatt 2 eingefügt.
5. Das Ganze soll sich einige Male wiederholen lassen (mehr als 10 Wiederholungen werden es aber wahrscheinlich nie werden)


Ich habe zwar keine Ahnung von VBA, kenne mich mit Programmierung in anderen Sprachen aber ein Stück weit aus. Daher würde ich das Ganze mit einer Variable für die Zeile, ab der der Bereich eingefügt werden soll, angehen. Die sollte dann einen bestimmten Startwert haben und bei jedem Klick auf den Button um einen festen, gleich bleibenden Werte erhöht werden.

Ich weiß nichts über die allgemeine Struktur von VBA-Codes und Variablen, also wäre ich euch sehr dankbar, falls mir jemand dabei helfen könnte.

Vielen Dank im Voraus und herzliche Grüße,

VodeAn

[Heinz Ulm]

Hallo VondeAn,

in meinem Beispiel in der anderen Frage ist das gelöst.

[chris-ka]

Hi,

...diesmal aber 5 oder 6 Zeilen, tja

Code:

Dim lng_Last_R As Long
With Worksheets("Tabelle2")
lng_Last_R = .Cells(.Rows.Count, "E").End(xlUp).Row
Worksheets("Tabelle3").Range("A1:D4").Copy _
     Destination:=.Range("E" & WorksheetFunction.Max(3, lng_Last_R) + 2)
End With

[VodeAn]

Hallo VondeAn,

in meinem Beispiel in der anderen Frage ist das gelöst.

Wirklich? Dann hatte ich das entweder übersehen oder so nicht realisiert. Vielen Dank!

[VodeAn]

Hi,

...diesmal aber 5 oder 6 Zeilen, tja

Code:

Dim lng_Last_R As Long
With Worksheets("Tabelle2")
lng_Last_R = .Cells(.Rows.Count, "E").End(xlUp).Row
Worksheets("Tabelle3").Range("A1:D4").Copy _
     Destination:=.Range("E" & WorksheetFunction.Max(3, lng_Last_R) + 2)
End With

Hi Chris,

das hat leider nicht den gewünschten Effekt. Muss ich dabei noch irgendwas beachten?


@Heinz Ulm:

Ich habe deine Lösung leider nicht verstanden. Ich fürchte, ich bin nicht besonders gut darin, Code einer mir unbekannten Sprache zu analysieren :D

Wärst du so nett, mir zu erklären, wie das funktioniert? Kurzfassung reicht :)

Vielen Dank, ihr beiden!

Grüße VodeAn

[Heinz Ulm]

Hallo VodeAn,

hier mal der Code mit Anmerkungen:

Code:

Sub ChefdatenSpeichern()
    
    Dim Loletzte As Long
    
    Sheets("Daten").Select
    'Festlegung der nächsten freien Zeile auf der Tabelle "Daten"
    Loletzte = IIf(IsEmpty(Cells(Rows.Count, 2)), Cells(Rows.Count, 2).End(xlUp).Row, Rows.Count) + 1
    
    'Kopieren der Eingabedaten auf Zeile 2 und Ubertrag der Werte in die Tabelle Daten
    Sheets("Eingabe").Select
    Range("A2:L2").Copy
    Sheets("Daten").Select
    Range("B" & Loletzte).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    'Kopieren der Formel in der Spalte A der Tabelle Daten vor die eingefügten Werte
    
    Range("A" & Loletzte - 1).Copy
    Range("A" & Loletzte).Select
    ActiveSheet.Paste
    
    'Wechsel auf die Tabelle Eingabe
    
    Sheets("Eingabe").Select
    
    'Aufruf des Makros Uebersicht
    Uebersicht
    
    'Löschen der Eingabewerte
    Range("C7:C10,C14:C16,C18:C21").ClearContents
End Sub
Sub Uebersicht()
  
   Dim Anzahl As Integer
   Dim ZZeile As Integer
   Dim ZSpalte As String
   Dim kZeile As Integer
  
   'Zuweisen der Werte für die Variablen aus den Zellen der Tabelle Eingabe
   Anzahl = Range("F3").Value
   ZZeile = Range("F4").Value
   ZSpalte = Range("H3").Value
   kZeile = Range("H4").Value
  
   'Kopieren des Bereiches F5:L22
    Range("F5:L22").Select
    Selection.Copy
    
    'Festlegung des Einfügepunktes, welche Spalte (Zspalte = der Wert aus der H3 und
    ' der Zeile ZZeile =Wert aus der Zelle F4
    Range(ZSpalte & kZeile).Select
    
    'Einfügen der Wetre und Formate des kopierten Bereiches
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("N5").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        
    'Erhöhung der Anzahl und der Zeilen für die nächste Einfügeoperation
    Anzahl = Anzahl + 1
    ZZeile = ZZeile + 19
    
    'Zurückschreiben der erhöhten Werte in die Zellen
    Range("F3").Value = Anzahl
    Range("F4").Value = ZZeile
    
    'Überprüfung der anzahl der Einfügungen, wenn Anzahl = 8 dann die Meldung("Es.... werden")
    If Anzahl = 8 Then
    MsgBox ("Es kann keine weitere Übersicht eingefügt werden")
    
    'Zurücksetzen der Werte auf die Ausgangsposition
    Range("F3").Value = 0
    Range("F4").Value = 5
    End If
End Sub

und zu den Formeln in der Tabelle:



Eingabe

	E	F	G	H
3	Anzahl	0	Spalte	N
4	Zeile	5		5

verwendete Formeln
Zelle	Formel	Bereich	N/A
H3	=WENN(F3<4;"N";"V")
H4	=WENN(H3="V";F4-76;F4)

Excel-Inn.de

Hajo-Excel.de

XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007

Add-In-Version 14.24 einschl. 64 Bit

Die Zellen F3 und F4 werden von dem Code gefüllt, in der H3 wird der Spaltenbuchstabe festgelegt, dass nach 4 Einfügungen (Anzahl = 0 bis 3) in spalte N, danach in spalte V die Kopie eingefügt wird.

In der H4 berechne ich wieder in Abhängigkeit des Spaltenbuchstaben die Einfügezeile.

Wenn du noch was wissen willst frage einfach nochmals.

[chris-ka]

Hi,

der Code macht eigentlich das was du beschrieben hast...

das hat leider nicht den gewünschten Effekt. Muss ich dabei noch irgendwas beachten?

Code:

damit kann ich leider nichts anfangen, welcher Effekt passt nicht?

[VodeAn]

Vielen lieben Dank. Das ist schon ziemlich 'overpowered' :D

Da ich inzwischen zu dem Schluss gekommen bin, dass ich gar keinen Eingabebereich mehr haben will, wie das in de anderen Frage am Anfang noch der Fall war, sondern einfach nur in der Übersichtssektion neue, leere Bereiche einfügen möchte, die man dann von Hand ausfüllt, ist das jetzt fast zu viel des Guten. Wie würde denn jetzt der Code aussehen, wenn wir uns wirklich nur darauf beschränken, einen Bereich zu kopieren und mit Versänderung der Position einzufügen?

Der Startwert muss nicht automatisch ermittelt werden oder so was, den kann ich auch einfach selbst festlegen. Die Größe des Bereichs beträgt ja 4x4, also sagen wir mal, jeder Bereich wird 5 Zeilen unter dem letzten eingefügt, damit da noch etwas Platz dazwischen ist...

Vielen Dank für deine Geduld! :)

[Käpt'n Blaubär]

Hallo,

Ich habe deine Lösung leider nicht verstanden. Ich fürchte, ich bin nicht besonders gut darin, Code einer mir unbekannten Sprache zu analysieren
Wärst du so nett, mir zu erklären, wie das funktioniert? Kurzfassung reicht

1. weiß ich jetzt nicht, ob ich mit dem Richtigen meckere, ich mache es trotzdem
Ein Code beginnt mit Sub xxx() ... und endet mit End Sub und nicht irgendwo mittendrin.
Nur so kann der Helfende alle Befehle im VBA-Code wirklich überprüfen.

2. zu Deiner angeforderten Kurzfassung (zum Verständnis und drum ohne irgendwelchen SchnickSchnack):

Der Befehl, mit dem Du die letzte beschriebene Zeile eine Spalte eines bestimmten Blattes anzeigen kannst, heißt

Dim Zeile as Long ' Laufvariable

With Worksheets("Tabelle2") ' weil Du von Blatt 2 lesen willst

Zeile = .Cells(.Rows.Count, 5).End(xlUp).Row ' die "5" steht für die Spalte "E"
' ermittelt wird hier die letzte bereits beschriebene Zelle

MsgBox Zeile ' das kannst Du beispielsweise mit dieser Meldung problemlos prüfen
' (da diese Zeile nicht wirklich gebraucht wird, kannst Du sie löschen)

Zeile = .Cells(.Rows.Count, 5).End(xlUp).Row +1 ' Da Du die erste Leere Zeile brauchst, um in diese etwas zu kopieren,
' zählst Du auf die ermittelte Zeile noch "+1" hinzu und kannst nun
' Deine Daten da hin kopieren
' (die erste mit Zeile = beginnende Zeile ist dann übrigens auch überflüssig
' und kann gelöscht werden)
End With

Im Übrigen würde ich persönlich es vorziehen, Dich mit Deinem Vornamen ansprechen zu können

Ups ... ich habe da mal was probiert, aber die Formatierung ist nicht so, wie ich das erwartet habe.
Ich versuche das zu ändern

So, Sorry nochmal. weie gesagt, was probiert und nicht für gut befunden.
So sollte das wirklich aussehen. https://www.dropbox.com/s/muc8032m12z8fy...0Zeile.jpg

[VodeAn]

Ein Code beginnt mit Sub xxx() ... und endet mit End Sub und nicht irgendwo mittendrin.
Nur so kann der Helfende alle Befehle im VBA-Code wirklich überprüfen.

Da hast du recht, tut mir leid.

Wenn ich dich richtig verstehe, soll der Code dann also so aussehen?

Code:

Dim Zeile as Long
With Worksheets("Tabelle2")
Zeile = .Cells(.Rows.Count, 5).End(xlUp).Row
Zeile = .Cells(.Rows.Count, 5).End(xlUp).Row +1
End With

Wie sähe dann der komplette Code mit Kopieren und Einfügen aus? An welcher Stelle muss dieses Segment denn rein?

Danke und Grüße,

Max