Office: Daten kopieren und in nächste frei Zelle einfügen

Hallo liebe User,

ich bin schon seit einiger Zeit auf der Suche nach der richtigen Lösung.

Ich hoffe das ihr mir weiterhelfen könnt.

Ich habe ein Tabellenblatt mit dem Namen „Daten1“

Und ein Tabellenblatt mit dem Namen „Archiv“

Im Tabellenblatt „Daten1“ können im Bereich A4:N13 Daten (Rechnungsdaten) eingegeben werden.

Dabei kommt es vor, dass nicht alle Zeilen beschrieben werden, z.B. wenn ich in einem Monat nur eine Rechnung erhalte (A4:A13).
Es muss also vor dem Kopieren ermittel werden welche Zeilen beschrieben sind.

Wenn ich die Rechnung bezahlt habe, dann möchte ich die Daten aus Zeile A4:A13 kopieren und in das „Archiv“ in Zelle A2 (ohne Formeln) einfügen und die Daten in“Daten1“ löschen


Nun kommt das eigentliche Problem:

Wenn ich nun im nächsten Monat 2 ,3 oder 4 Rechnungen eintrage, (A4:N4 , A5:N5,A6;N6) dann sollen diese wieder Kopiert werden und in das Archiv eingefügt werden, allerdings unter dem letzten Eintrag vom Vormonat.

Und so weiter.

Ich hoffe ich konnte das Problem einigermaßen verständlich erklären.


Ich hoffe ihr könnt mir weiterhelfen.


LG Andreas

 

Hallo Andreas,

1) nach meinem Deutsch- bzw. Excelverständnis stellt der Bereich A4:A13 eine Spalte dar. Soll also ein Spaltenbereich aus "Daten1" nach "Archiv" kopiert werden oder handelt es sich in Wahrheit tatsächlich um einen Zeilenbereich A4:N4, wie du weiter unten schreibst?
2) Soll die Zelle A2 im Arbeitsblatt "Archiv" (offenbar) die Anfangszelle der Speicherungen sein? Sollen diese Speicherungen dort nun zeilenweise oder spaltenweise erfolgen?

 

Hallo Anton Exl,
ich verstehe deine Verwirrung, und zurecht, denn ich habe mich verschrieben. Aber ich meine tatsächlich beides.
Wie ich schon versucht habe zu erklären, dass es vorkommt, dass nur eine Spalte A4:A13 zu kopieren ist oder 2 oder drei Spalten. es kommt aber auch vor, dass alle spalten, also der ganze Bereich A4:N13 kopiert werden muss und in das Archiv eingefügt werden soll. In jedem Fall muss vorher geprüft werden welche Zeile mit daten gefüllt sind, dass auch nur diese kopiert werden.
Denn sonst habe ich den Effekt, dass ich immer den ganzen Bereich A4:N13 kopiere und in das Archiv enfüge. Dabei wird aber immer auch der Bereich kopiert, der nicht beschrieben ist und wenn ich die selbe aktion nochmals ausführe, dann wird die neue Kopie nicht direkt unter der anderen eingefügt, sondern 10 Spalten darunter und es bleiben unter umständen 9 spalten leer.aber das ist eben nicht das was ich möchte.

Ich hoffe das es jetzt etwas verständlicher ist und entschuldige mich für das Durcheinander.

LG
Andreas
PS wenn es hilft, dann würde ich meine Datei hochladen

 

Hallo Andreas,

ich habe dir eine Exceldatei mit einem Makro "Kopieren_insArchiv" im allgemeinen Codemodul "Modul1" angefügt.
Dabei enthält das Arbeitsblatt "Daten1" den Button "Kopieren ins Archiv", der mit diesem Makro verknüpft ist.

Bei Klick auf den Button müsste das Makro das tun, was du beschrieben hast: Zeilen des umrandeten Bereichs, die Daten enthalten, im Arbeitsblatt "Archiv" an die Vorgängerdaten anzufügen. Es werden nur Werte kopiert, keine Formeln.

Folgender Makro-Code ist enthalten:

Code:

Option Explicit

Sub Kopieren_insArchiv()
    Dim WsQ As Worksheet, WsZ As Worksheet
    Dim rgZeile As Range, rgZelle As Range

    Set WsQ = Worksheets("Daten1")
    Set WsZ = Worksheets("Archiv")

    For Each rgZeile In WsQ.Range("A4:N13").Rows

        For Each rgZelle In rgZeile.Cells

           If (Not IsEmpty(rgZelle)) Or Len(rgZelle) Then
              rgZeile.Copy
              GetNextLeereZelle(WsZ).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
              Exit For
           End If

        Next rgZelle

    Next rgZeile

    Application.CutCopyMode = False
  
End Sub
Function GetNextLeereZelle(Ws As Worksheet, Optional Sp As Long = 1) As Range
    With Ws.UsedRange
      Set GetNextLeereZelle = .Rows(.Rows.Count + 1).Cells(Sp)
    End With
End Function

 

Hallo Anton Exl,
Vielen, vielen Dank für die Lösung, dass ist genau das was ich benötig.

Du bist der Beste.

LG
Andreas

 

Hallo Anton Exl,

Mein Freude war wohl zu früh.
Ich habe dein Beispiel bei mir in der Datei angewendet, dort allerdings funktioniert es nicht.
Das was kopiert wird wird im Archiv zwar eingefügt, jedoch in Zeile 133.
Ich würde Die meine Datei senden.
Vielleicht kannst Du ja da nochmal drüber schauen.

Vielen Dank im voraus.
LG
Andreas

 

Hallo Andreas,

da gab es ein paar Probleme, von denen du mir nicht berichtet hast in deinen Postings:

1) Eine Excel-Zelle ist nicht deshalb schon leer, weil nichts angezeigt wird:
Wenn zB. in Daten1!A5 eine leere Zelle angezeigt wird, jedoch in ihr die folgende Formel =WENN(B5<>"";JETZT();"") enthalten ist, so folgt daraus zwingend, dass sie entweder einen Datumswert oder aber eine ""-Zeichenkette enthält. Somit enthält sie also keinen EMPTY-Wert.
Wenn zB. in Daten1!H5 folgende Formel =G5*70/100 enthalten ist, wobei offenbar G5=EMPTY, so enthält H5=0, was keinesfalls LEER ist, sondern eben 0 ist, auch wenn durch die Formatierung eine Leerzelle vorgetäuscht wird.

Ich habe jetzt eine benutzerdefinierte Funktion "IstQuasiLeerzelle(Zelle)" eingefügt, die diese unterschiedlichen Quasi-Leerwerte ermittelt.

2) Was das Zeilenproblem im Blatt "Archiv" betrifft:
Das Makro überprüft, wo im Blatt "Archiv" der benutzte Bereich (UsedRange) liegt und ermittelt damit die nächste Zeile als erste Leerzeile, in die die jeweilige Daten1-Zeile wertmäßig zu kopieren ist.
Sind da jedoch übers Arbeitsblatt verteilt diverse Pseudo-Leerzellen (vgl. Punkt 1)) enthalten, so wird das als nicht-leerer, also benutzter Bereich gewertet.
Ich habe den Inhalt des Arbeitsblattes "Archiv" gelöscht, ohne den enthaltenen Button zu entfernen, und jetzt funktioniert das Makro wie gewünscht.

3) Das Makro sieht jetzt wie folgt aus (ist in der Datei bereits enthalten):

Code:

Option Explicit

Sub Kopieren_insArchiv()
    Dim WsQ As Worksheet, WsZ As Worksheet
    Dim rgZeile As Range, rgZelle As Range

    Set WsQ = Worksheets("Daten1")
    Set WsZ = Worksheets("Archiv")

    For Each rgZeile In WsQ.Range("A4:N13").Rows

        For Each rgZelle In rgZeile.Cells

           If Not IstQuasiLeerzelle(rgZelle) Then
              rgZeile.Copy
              GetNextLeereZelle(WsZ).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
              Exit For
           End If

    Next rgZelle, rgZeile

    Application.CutCopyMode = False
 
End Sub
Function GetNextLeereZelle(Ws As Worksheet, Optional Sp As Long = 1) As Range
    With Ws.UsedRange
      Set GetNextLeereZelle = .Rows(.Rows.Count + 1).Cells(Sp)
    End With
End Function
Function IstQuasiLeerzelle(rgZelle As Range) As Boolean
    Select Case VarType(rgZelle.Value)
      Case vbEmpty:   IstQuasiLeerzelle = True
      Case vbString:  IstQuasiLeerzelle = Len(rgZelle.Value) = 0
      Case vbInteger, vbLong, vbSingle, vbDouble, vbCurrency, vbByte:
                      IstQuasiLeerzelle = (rgZelle.Value = 0)
      Case vbDate:    IstQuasiLeerzelle = (rgZelle.Value = 0)
      Case vbBoolean: IstQuasiLeerzelle = (rgZelle.Value = False)
      Case Else:      IstQuasiLeerzelle = True
    End Select
End Function

 

Hallo Anton Exl,
Ich weis überhaupt nicht was ich sagen soll, vielen lieben Dank für Deine Mühe und vor allem für Deine Zeit.
Ich wäre im Leben nicht darauf gekommen, dass eine Zelle nicht leer ist, wenn eine Formel drin steht.
Jetzt kann ich endlich mit meiner kleinen Datei weitermachen.

Habe noch einmal vielen Dank und bleib gesund.

LG
Andreas