Office: Letzte Zeile kopieren und darunter einfügen

Wenn die Zielzelle (also die Zelle die angeklickt wird) eine Formel hat, dann wird die Zelle rechts daneben ausgewählt - das passiert so lange, bis eine Zelle ohne Formel gefunden wird und dort bleibt der Cursor stehen.

 

Jetzt habe ich deinen Code und einen Code von mir in der Tabelle.

Bekomme eine Fehlermeldung wegen dem doppeltem Namen. Kann ich die zwei Codes irgendwie zusammen fügen?


Private Sub Worksheet_Change(ByVal r As Range)

s = r.Column
rr = r.Row
If s = 9 Then
Cells(rr, s + 1).Value = Time

End If

End Sub


Der Code gibt mir die Uhrzeit wieder, wenn eine Zelle davor ein Eintrag getätigt wurde





Ah ok, danke für die Erklärung

 

Ja, prinzipiell kann/muss man beide Codes zusammenführen. Für welche Spalten soll der Code gelten?

 

Also die ganze Zeit hatte ich es so:

Private Sub Worksheet_Change(ByVal r As Range)

s = r.Column
rr = r.Row
If s = 9 Then
Cells(rr, s + 1).Value = Time

End If

s = r.Column
rr = r.Row
If s = 11 Then
Cells(rr, s + 1).Value = Time

End If

s = r.Column
rr = r.Row
If s = 13 Then
Cells(rr, s + 1).Value = Time

End If

s = r.Column
rr = r.Row
If s = 15 Then
Cells(rr, s + 1).Value = Time

End If

s = r.Column
rr = r.Row
If s = 17 Then
Cells(rr, s + 1).Value = Time

End If

End Sub

Für die Spalten 9, 11, 13, 15 und 17

 

Dann so:

Code:

 

Ich danke dir.
Da wäre ich nie drauf gekommen, das man es auch so kurz schreiben kann.

Bin sprachlos. Tausend dank für deine wirklich GROßE Hilfe.

 

Habe ein paar Testdaten in meine Tabelle eingegeben, dabei ist mir aufgefallen, dass mir der Code jetzt meine Bedingte Formatierung in Spalte S zerhaut.

Habe für diese Spalte mehrere bedingte Formatierung.
Hier geht es speziell um Doppelte Werte.

In meiner Zeile 6 habe ich ja meine Grund Formatierung die dann bei einer Eingabe in Spalte 4 eine Zeile nach unten kopiert wird.

Die bedingte Formatierung soll in den Zeilen =$S$6:$S$65

Aber nach der automatischen Kopierung z.B. bis in Zeile 9 lautet sie jetzt in der Zeile 6 =$S$6;$S$9§:$S$65

und in Zeile 7 =$S$7
in 8 =$S$8
in 9 =$S$9

aber so wird leider nicht mehr nach doppelten Werten von Zeile 6 bis 65 gesucht

 

In Spalte S gibt es in deiner Mappe keine bedingte Formatierung, deshalb kann ich dir auch nicht weiterhelfen - dazu müsstest du schon die Mappe mit der bedingten Formatierung hochladen.

 

Habe es mal versucht so zu lösen

Sub doppelte_färben()
Dim zelle As Range For Each zelle In ActiveSheet.Range("A1:A100") If WorksheetFunction.CountIf(ActiveSheet.Range("A1:A100"), zelle.Value) > Then
zelle.Interior.ColorIndex = 3 Next zelle
End Sub

Jedoch werden mir da alle Zeilen in der Spalte S rot makiert, weil da in den Zeilen Formeln stehen.
Vielleicht gibt es ja dafür eine Lösung, dass nur die Zeilen mit gleichen Zahlen rot werden.

Sonst könnte ich hier erst übermorgen die Tabelle mit den bedingten Formatierungen hochladen.

 

Ich würde es anders lösen:

1. die bedingte Formatierung nicht erst ab Zeile 6 sondern ab Zeile 5 erstellen, sodass Zeile 5 als Referenzzeile gelten kann.

2. im Change-Ereignis nach dem Kopieren die bedingte Formatierung ab Zeile 6 löschen und anschließend in Zeile 5 den Bereich der bedingten Formatierung neu definieren bis zur letzten belegten Zeile.

 

Kannst du mir vielleicht erklären wie das geht. Ich weiß nicht wie ich daran gehen muss.

Kann ein Bild mit anhängen, in dem sieht man die bedingte Formatierung.

Wo du gerade Zeile 5 erwähnst.

Da hätte ich noch eine Frage die mir gerade einfällt.

Du hattest mir ja eine sehr gute Lösung erstellt, in der immer die letzte Zeile weiter nach unten kopiert wird.
Kann man es auch so programmieren, dass in der Zeile fünf meine Formeln stehen und diese dann immer kopiert wird?

Also nicht wie jetzt:

Eintrag in D6, mit Enter bestätigen und Zeile 6 wird in Zeile 7 kopiert;
Eintrag in D7, mit Enter bestätigen und Zeile 7 wird in Zeile 8 kopiert

sondern,

Eintrag in D6, mit Enter bestätigen und Zeile 5 wird in Zeile 7 kopiert;
Eintrag in D7, mit Enter bestätigen und Zeile 5 wird in Zeile 8 kopiert

So das die Grundformeln in Zeile 5 stehen und immer nur diese Zeile kopiert wird

 

Hoffe ich frage nicht zu viel :-(

Die Tabelle ist sehr groß und Excel kann die ganzen Daten nicht gut verarbeiten kann.
Ich habe sehr viele Formeln in der Tabelle. Entweder Excel lädt zu lang aber meistens hängt es sich auf.

Deswegen habe ich den Code noch eingebaut

Sub Formel_in_Wert()
Range("AC6:HZ65") = Range("AC6:HZ65").Value
End Sub

Wenn ich in D6 meine Eingaben getätigt habe und alle Formeln ihre Arbeit gemacht haben, lösche ich damit die Formeln und behalte nur noch die Ergebnisse, dadurch läuft Excel stabiler und schneller.

Jedoch fällt mir da auf, das ja auch die Formeln der neuen leeren Zeile gelöscht werden.

Kann man es so programmieren, das die letzte Zeile die gerade neu kopiert wurde, durch betätigen der Entertaste nicht betroffen ist?

Oder wäre es besser es über eine Schaltfläche zu machen, dass man dieser das Makro zuweist und die neue Zeile erst nach betätigen der Schaltfläche kopiert wird?

Ich hoffe du kannst meiner Frage folgen. :-) Danke

 

In Zeile 5 können keine Formeln stehen denn dort stehen die Überschriften.

Man kann die Formeln in der Zeile, aus der in die nächste kopiert wurde, immer durch die Werte ersetzen, sodass nur in der neu erstellten Zeile die Formeln erhalten bleiben. Ersetze also in allen Zeilen ab Zeile 6 bis zur vorletzten Zeile die Formelergebnisse durch Werte.

Was die bedingten Formatierungen betrifft: trage bei allen $S$5 als Starzelle ein, dann kann man die bedingten Formatierungen ab Zeile 6 löschen und den Bereich neu eintragen.

Code:

 

Hallo Beverly,

tut mir leid, hatte die letzten zwei Tage keinen Laptop.
Danke SEHR für deinen sehr ausführlichen Code.
Den werde ich morgen direkt ausprobieren und dann schicke ich dir sehr gern mein Feedback.

Lg :-)

 

Hallo Beverly,

Danke, der Code funktioniert wirklich sehr gut und danke auch für den großartigen Tipp mit den bedingten Formatierungen.

Ich habe deinen Code für meine Verwendung etwas abgeändert.
Es läuft alles sehr gut.

Hatte am Anfang 8 oder 9 Fragen an dich, wo ich erst nicht weiter gekommen bin aber zum Glück konnte ich fast alle, bis auf drei Probleme selber lösen.

Jetzt hätte ich noch die drei (hoffentlich drei letzte :-) ) Fragen an dich, bei denen ich nicht weiter komme.

Hier mein bzw. dein Code und darunter meine Fragen.


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim lngLetzte As Long
Dim intZaehler As Integer
Select Case Target.Column
Case 4
If Target.Count = 1 Then
If Target "" Then
If Target.Offset(-1, 0) "" And Target.Offset(1, 0) = "" Then
lngLetzte = IIf(IsEmpty(Cells(Rows.Count, 3)), Cells(Rows.Count, 3).End(xlUp).Row, Rows.Count)
Application.EnableEvents = False

' kopieren in die nächste Zeile
Range(Cells(Target.Row, 2), Cells(Target.Row, 227)).Copy Cells(Target.Row + 1, 2)

' alle Zellen die keine formeln enthalten leeren
Range(Cells(Target.Row + 1, 3), _
Cells(Target.Row + 1, 23)).SpecialCells(xlCellTypeConstants).ClearContents

' Zellen kopieren und nur Werte einfügen
Range(Cells(Target.Row - 1, 3), Cells(Target.Row - 1, 23)).Copy
Cells(Target.Row - 1, 3).PasteSpecial Paste:=xlValues

' Zellen kopieren und nur Werte einfügen
Range(Cells(Target.Row - 1, 29), Cells(Target.Row - 1, 222)).Copy
Cells(Target.Row - 1, 29).PasteSpecial Paste:=xlValues

' alle Zellen die keine formeln enthalten leeren
Range(Cells(Target.Row - 1, 30), _
Cells(Target.Row - 1, 222)).ClearContents

' Kopiermakierung aufheben
Application.CutCopyMode = False

' in Spalte C nächste Numemr eintragen
Cells(Target.Row + 1, 3) = Cells(Target.Row, 3) + 1

' wenn S5 eine bedingte Formatierung enthält
If Cells(5, 19).FormatConditions.Count > 0 Then

' alle bedingten Formatierungen ab Zeile 6 löschen
Range(Cells(6, 19), Cells(lngLetzte, 19)).FormatConditions.Delete

' Schleife über alle bedingten Formatierungen Zelle S5
For intZaehler = 1 To Cells(5, 19).FormatConditions.Count

' neuen Bereich festlegen
Cells(5, 19).FormatConditions(intZaehler).ModifyAppliesToRange Range("S$5:$S$" & lngLetzte)
Next intZaehler
End If
Application.EnableEvents = True
End If
End If
End If
Case 9, 11, 13, 15, 17
Target.Offset(0, 1).Value = Time
End Select
End Sub



'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Cells(1).HasFormula Then Target.Offset(0, 1).Select
'End Sub



' Frage 1: wenn in Zelle D=4 der eigene Eintrag irgendwann mal gelöscht werden sollte, sollen auch die Zelleninhalte von B=2, E=5, F=6, G=7, H=8, S=19, T=20, X=24, Y=25, Z=26, AA=27 und AB= 28 gelöscht werden. Also nur wenn in den Spalten E, F, G, H, S und T die Formeln in Werte umgewandelt wurden. In den Spalten B, X, Y, Z, AA und AB stehen Formeln, sollten dann aber auch zu diesem Zeitpunkt mit gelöscht werden.

' Frage 2: Zellen mit Formeln dürfen vom Anwender nicht aus wählbar sein, was schon gut funktioniert. Da jetzt aber die Formeln in den Zellen von E=5, F=6, G=7, H=8, S=19 und T=20 in Werte umgewandelt werden, sollen diese auch NICHT vom Anwender aus wählbar sein.

' Frage 3: Nach der Eingabe in der Zelle D6=4 soll die neue Zellenauswahl direkt auf D7=4 springen. Nach der Eingabe in D7=4 auf die Zelle D8=4 usw.




Ich würde mich über weiter Hilfe sehr freuen.

Grüße und noch einen schönen Sonntagabend :-)