Office: (Office 2016) Wenn Bedingung erfüllt, Zeile in neues Blatt verschieben (VBA)

Hallo zusammen,
ich habe es über VBA Programmierung hinbekommen, das wenn ich eine Zeile unter Spalte "Erledigt" auf "Ja" setze das er mir diese in eine neues Blatt kopiert, funktioniert prima.
Nur würde ich es jetzt gerne auf dem neuen Blatt umgekehrt machen, das wenn einer aus Versehen Erledigt auf "Ja" gesetzt hat, das ich im neuen Blatt auf "Nein" setze, die Zeile wieder in das ursprüngliche Blatt zurück geschoben wird, entsprechend der KW.
Bekomme es leider selbständig nicht hin.
Bitte um eure Hilfe!

 

Hallo,

man kann einfache Dinge auch kompliziert machen. Da reicht doch eine Liste völlig aus, die kann man nach ja bzw. nein Filtern und alles ist OK.

 

Hallo

 

ja, das ist das Problem, so wie du vorschlägst funktioniert das nicht.
Das ist unsere interne Fertigungsliste, und sobald ein Teil gefertigt ist soll es von der Hauptseite verschwinden.
So sehen alle die fertigen Teile auf dem 2. Blatt.
Wie beschrieben kann der Fertigungsleiter oder einer seiner Mitarbeiter aus Versehen auf "Ja" setzen, dann ist der Eintrag weg.
Die Datei enthält im Laufe des Jahres mal um die 5000 Einträge.
Wäre nett wenn jemand helfen könnte.

Dankeschön

 

Hi Franky,

weshalb machst du es nicht mit einer Abfrage, ob die Daten verschoben werden sollen? Dann sparst du dir den ganzen Aufwand, Daten wieder zurück übertragen zu müssen. Ein "Ja" kann man versehentlich schnell mal setzen, aber eine danach erfolgende Abfrage sollte einen schon aufmerksam machen:

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim lngErste As Long
    Dim strJaNein As String
    If Target.Column = 8 Then
        If Target.Count = 1 Then
            If UCase(Target) = "JA" Then
                strJaNein = MsgBox("Daten wirklich verschieben?", vbYesNo)
                If strJaNein = vbYes Then
                    With Worksheets("gefertigte Teile")
                        lngErste = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
                        .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
                        Rows(Target.Row).Copy
                        .Cells(lngErste, 1).PasteSpecial Paste:=xlValues
                        Rows(Target.Row).Delete shift:=xlUp
                    End With
                End If
            End If
        End If
    End If
End Sub

---

 

Hi Beverly,

die Lösung ist natürlich der Hammer!!

Ganz herzlichen Dank!

 

Hi Berverly,
ich habe mal wieder ein VBA Problem in meiner EXCEL Tabelle, bei der alten habe ich es wenigstens hinbekommen das es mir die Zeilen in ein bestimmtes Blatt verschiebt wenn die Bedingung erfüllt ist.
Aktuell macht der mir das bei meiner EXCEL nicht.
Könntest du bitte mal drüber schauen wo ich den Fehler eingebaut habe?

Wenn ein Werkstatt-Projekt erledigt ist sollte es es in das Blatt "erledigte Wkst.Projekt schieben.
Natürlich wieder mit deiner oben eingebauten, zusätzlichen Abfrage ob er wirklich verschieben soll.

Es wäre nett wenn du mir nochmal helfend zur Seite stehen könntest.

Danke vorab für deine Hilfe und ein schönes Wochenende.

Gruß Franky

 

Moin

Vielleicht solltest du noch mal den Vorschlag von Klaus-Dieter in #2 in Erwägung ziehen.
Zumindest hättest du dann neues "Problem" nicht.

 

Moin,

wie schon weiter oben beschrieben geht das auf Grund der hohen Anzahl nicht, die Liste wird unübersichtlich.

So sieht man welche Projekte abgeschlossen sind.

Grüße

 

Die Anzahl spült keine Rolle.
Und wenn man filtert sieht man auch welche Projekte abgeschlossen sind.

 

Pass auf, gleich kommt das Argument "Aber meine User sind so dumm, die wissen nicht , wie man den Autofilter benutzt"

Hier mal noch eine andere Meinung/Klarstellung, die sich in dieser Form zwar auf Monatsblätter bezieht, aber im Grunde genau dasselbe meint: www.excel-ist-sexy.de/mehrere-tabellenblaetter-1-auswertung

Nochmals: Lass die Daten auf einem Blatt, das ist schlicht und ergreifen einfacher und besser. Und falls du Bedenken wegen dem Autofilter hast: Eine Anwenderschulung wirkt Wunder.
Falls es dennoch zu umständlich erscheint: Excel kennt auch benutzerdefinierte Ansichten, die sich auf Knopfdruck abrufen lassen. Oder mach dir Makros, die den Autofilter füttern statt Daten durch die Gegend zu kopieren. Das Hin- und Herkopierten ist viel zu Fehleranfällig.

 

So ganz kurz, das hat überhaupt nichts mit dumm zu tun oder das einer nicht kann oder will.
Aber anscheinend kennen viele User hier unsere Gegebenheiten im Unternehmen, womit ich mich Frage, warum antwortet man dann überhaupt, oder Stand da irgendwo ich zwinge euch zu antworten?
Das hat seinen Grund warum ich das gerne über VBA geregelt hätte, und damit Ende der Diskussion.

Wenn jemand helfen will wäre ich sehr dankbar dafür, alle die nicht wollen, auch Recht.

Schönes Wochenende.

 

Die Erfahrung zeigt, dass man mit der Zeit betriebsblind wird und daher bessere Lösungen schlicht nicht mehr wahrnimmt.
Übrigens ist das Argument: „will ich so, machen wir schon immer so, basta“ genauso typisch wie „meine User kapieren das nicht“.

Brauchst dich übrigens nicht weiter über mich zu ärgern, ich werde dazu jetzt nichts mehr schreiben.

 

Hi Frank,

der Code kann deshalb nicht funktionieren, weil er in deiner 1. Mappe ausgelöst wird, wenn in Spalte H mit der Zeile If Target.Column = 8 Then der Begriff "ja" (bzw. "JA") eingetragen wird - Zeile If UCase(Target) = "JA" Then. Und in deiner jetzigen Mappe heißt deine Zieltabelle nicht mehr "gefertigte Teile" sondern "erledigte Wkst. Projekte". Dies alles muss natürlich entsprechend angepasst werden. Außerdem soll der Code für mehrere Tabellenblätter gültig sein - damit er nicht in die Codemodule jedes Blattes eingetragen werden muss, kann man ihn entsprechend umschreiben, sodass er gleichzeitig für alle Blätter gilt - dazu benutzt man dann nicht mehr das Worksheet_Change-Ereignis des Tabellenblattes sondern das Workbook_SheetChange-Ereignis, welches dann (nur) im Codemodul DieseArbeitsmappe stehen muss, da es ja für die gesamte Arbeitsmappe gelten soll. Allerdings muss dabei beachtet werden, dass du 2 Blätter hast, in denen der Code nicht ausgeführt werden soll - diese muss man dann natürlich ausnehmen. Das macht man am besten mit einer Select Case-Anweisung. Der komplette Code sieht dann so aus:

Code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lngErste As Long
    Dim strJaNein As String
    Select Case Sh.Name
        Case "erledigte Wkst. Projekte", "Konstanten"
            'mache nichts wenn diese beiden Blätter
        Case Else
            'für alle anderen Blätter
            If Target.Column = 7 Then
                If Target.Count = 1 Then
                    If UCase(Target) = "ERLEDIGT" Then
                        strJaNein = MsgBox("Daten wirklich verschieben?", vbYesNo)
                        If strJaNein = vbYes Then
                            With Worksheets("erledigte Wkst. Projekte")
                                lngErste = IIf(IsEmpty(.Cells(.Rows.Count, 1)), _
                                .Cells(.Rows.Count, 1).End(xlUp).Row, .Rows.Count) + 1
                                Range(Cells(Target.Row, 1), Cells(Target.Row, 7)).Copy
                                .Cells(lngErste, 1).PasteSpecial Paste:=xlValues
                                Rows(Target.Row).Delete shift:=xlUp
                            End With
                        End If
                    End If
                End If
            End If
    End Select
End Sub

Außerdem ist mir aufgefallen, dass in deiner Gültigkeitsliste in Spalte G sehr viele Leerzeilen sind, weil du als Quelle =Konstanten!$E$2:$E$20 angegeben hast und nur die Zelle E2:E6 tatsächlich mit Daten belegt sind. Wenn du dir nicht sicher bist, ob irgendwann mal weitere Bemerkungen hinzukommen sollen, dann solltest du den Bereich für die Quelle dynamisch gestalten, damit nur die tatsächlich gefüllten Zellen angezeigt werden und keine Leerzeilen. Das kannst du realisieren, indem du im Namensmanager (Register Formeln) einen Namen definierst (z.B. "Bemerkung") und unter "Bezieht sich auf:" diese Formel eingibst: =Konstanten!$E$2:INDEX(Konstanten!$E:$E;ANZAHL2(Konstanten!$E:$E);1). In jeder Gültigkeitszelle musst du außerdem anstelle =Konstanten!$E$2:$E$20 nun dieses eintragen =Bemerkung - das lässt sich ja relativ schnell auf jedem Blatt ausführen, indem du beim Eintragen in die erste Zelle das Häkchen setzt bei "Änderungen auf alle Zellen mit den gleichen Einstellungen anwenden".

---

 

Hallo Beverly,

ich habe jetzt alles nach deiner Anleitung gemacht, auch noch mal auf Abstände und Schreibweise geachtet, das Makro funktioniert nicht.
Ich bekomme es nicht hin, keine Ahnung warum.
Bei meiner ersten EXCEL mit VBA habe ich es hinbekommen.
Das ist halt das Problem wenn man nicht täglich mit EXCEL schafft, bzw. schaffen muss.
Könntest du bitte nochmal drüber schauen?

Danke vorab für deine Mühe und ein schönes Wochenende.

Gruß Franky