Office: Makro braucht eine Abfrage weiss aber nicht wie :(

Hallo zusammen,

ich habe hier ein Kopiermakro das eigentlich gut funktioniert. Der erste Teil deklariert einfach nur die arbeitsblaetter u. der zweite teil sagt: "kopier mir die daten in die naechste freie spalte auf dem Zieldatenblatt. Koennern faellt auf dass die Zieldatenbank gerade "w3" ist. Das sollte ersetzt werden.

Zum Problem:

Meine datei ist ein aufgabendefinierer fuer meine kollegen. im arbeitsblatt "pool" schreibt jeder rein was so anfaellt. 1 mal die woche teilen wir dann die aufgaben zu. Jeder kollege hat jetzt ein eigenes arbeitsblatt mit seinem namen. In dem arbeitsblatt ist genau die gleiche tabelle wie im arbeitsblatt pool.

Der tabellenaufbau ist in spaltenform:
verantwortlich / task / status / beschreibung / prioritaet / due date

Die spalte unter "verantwortlich" wurde ueber data validation zum drop down gemacht. Auswahlmoeglichkeiten sind alle mitarbeiter (sagen wir von A - F)
Im meeting wird dem task dann ein verantwortlicher zugeteilt. wenn jetzt ein button auf dem arbeitsblatt aktiviert wird, soll die gesamte zeile mit task, beschreibung, etc. in das tabellenblatt des "verantwortlichen" kopiert werden. und zwar in die naechste freie Zeile.

schritt zwei soll sein wenn man mit dem formatierungsknopf "schrift durchstreichen (unterhalb der schriftart)" eine taskzeile durchgestrichen hat. dann soll sie die naechste freie Zeile im tabellenblatt "done" kopiert werden. (wir muessen die abgearbeiteten tasks am monatsende vorweisen). Allerdings immer noch durchgestrichen.

die zu kopierenden zellen stimmen nicht da es noch von einem alten makro ist.

ist soetwas moeglich?

hier der Code: leider nicht als solcher ausgewiesen da irgendwie die seite nicht fertig geladen hat.
Ich bin schon sehr auf eure rueckmeldung gespannt

PS: Excel 2010

(code)
Sub KopierListe()
Dim w1 As Worksheet, w3 As Worksheet
Set w1 = Worksheets("Pool")
Set w2 = Worksheets("A")
Set w3 = Worksheets("B")
Set w4 = Worksheets("C")
Set w5 = Worksheets("D")
Set w6 = Worksheets("E")
Set w7 = Worksheets("F")
Set w8 = Worksheets("Done")
With w3
lngRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
.Cells(lngRow, 2) = w1.Cells(3, 3)
.Cells(lngRow, 3) = w1.Cells(6, 3)
.Cells(lngRow, 4) = w1.Cells(6, 8)
.Cells(lngRow, 5) = w1.Cells(6, 11)
.Cells(lngRow, 6) = w1.Cells(6, 13)
.Cells(lngRow, 7) = w1.Cells(25, 16)
.Cells(lngRow, 8) = w1.Cells(27, 4)
.Cells(lngRow, 9) = w1.Cells(23, 14)
.Cells(lngRow, 10) = w1.Cells(24, 14)
.Cells(lngRow, 11) = w1.Cells(6, 14)
End With
End Sub

(/code)

 

Hallo,

jetzt mit Versuchsdatei. Vielleicht wird nun die Abfrage etwas verstaendlicher.

Ueber Hilfe wuerde ich mich sehr freuen.

Herzliche Gruesse

 

Hallo N123456789,

in der angehängten Datei wirst Du meine Änderungsvorschläge im Code finden.
Wenn ich nichts überlesen oder falsch verstanden habe, sollten Deine Vorgaben erfüllt sein.

Auf Erklärungen im Code habe ich mal verzichtet, da ich keine Ahnung bezüglich Deines Kenntnisstandes habe.
Schau es Dir mal an, und wenn Du etwas nicht verstehst erkläre ich es Dir gerne.

Ein paar Anmerkungen noch:

1. habe ich kein Excel 2010, sondern "nur" 2007. Vermutlich deshalb habe ich auch den

bei mir nirgendwo gefunden.
Deshalb habe ich mir bei meinen Tests mit der manuellen Formatierung geholfen. Ich hoffe es klappt bei Dir genauso.
Ich glaube nicht, das Dein Knopf ein Ereignis auslöst, welches man per Makro abfragen kann, deshalb habe ich erstmal das "selectionchange-Ereignis" benutzt.
Um mehrfaches kopieren der selben Zeile zu vermeiden, schreibe ich in Spalte "F" der jeweiligen Zeile das Wort "Done". Diese Zeile wird nicht noch einmal kopiert werden.
Ich bin mir eh nicht sicher ob Du "kopieren" oder "verschieben" meintest.
Sollen erledigte Aufgaben wirklich im jeweiligen Mitarbeiterblatt bestehen bleiben?

2. Den Bereich im Blatt "Pool", den Du mit Mitarbeiternamen füllen willst, hast Du durch die gelbe Markierung fest auf "A12:A16" gesetzt. Davon ausgehend würde ich im Makro folgende Schleife verwenden:

Code:

For lngCounter = 12 To 16
...
Next lngCounter

Soll dieser Bereich aber variabel nach unten gehen, würde ich es eher so machen:

Code:

lngCounter = 12
Do Until .Cells(lngCounter, 1) = ""
...
Loop

3. Voraussetzung ist natürlich, dass nirgendwo ein Blattschutz gesetzt wurde, dass das Blatt "Done" existiert, dass die Mitarbeiterblätter exakt genauso benannt sind wie die Mitarbeiternamen aus Deiner Liste, und das Du das Makro aus Blatt 2 in alle anderen Mitarbeiterblätter 1:1 kopierst.


So, und hier noch zum Mitlesen der jeweilige Code:

Code:

Option Explicit


Sub KopierListe()

Dim lngRow As Long
Dim lngCounter As Long
Dim objSheet As Object
Dim bolSheetName As Boolean


    With Me
        
        For lngCounter = 12 To 16
            bolSheetName = False
            For Each objSheet In ThisWorkbook.Worksheets
                If objSheet.Name = .Cells(lngCounter, 1).Value Then
                    With objSheet
                        lngRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                        .Cells(lngRow, 1) = Me.Cells(lngCounter, 1)
                        .Cells(lngRow, 2) = Me.Cells(lngCounter, 2)
                        .Cells(lngRow, 3) = Me.Cells(lngCounter, 3)
                        .Cells(lngRow, 4) = Me.Cells(lngCounter, 4)
                        .Cells(lngRow, 5) = Me.Cells(lngCounter, 5)
                        bolSheetName = True
                        Exit For
                    End With
                End If
            Next objSheet
            lngCounter = lngCounter + 1
            If bolSheetName = False Then MsgBox "Das Arbeitsblatt für den Mitarbeiter:" & vbNewLine _
                                                & .Cells(lngCounter, 1) & vbNewLine & "konnte nicht gefunden werden."
        Next lngCounter
        
    End With
    
End Sub

Code:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim lngRow As Long
    Dim objRow As Object

    With Me

        For Each objRow In UsedRange.Rows
            If .Cells(objRow.Row, 1).Font.Strikethrough = True And Not .Cells(objRow.Row, 6) = "Done" Then
                lngRow = ThisWorkbook.Worksheets("Done").Cells(.Rows.Count, 2).End(xlUp).Row + 1
                .Rows(objRow.Row).EntireRow.Copy Destination:=ThisWorkbook.Worksheets("Done").Rows(lngRow)
                Application.CutCopyMode = False
                .Cells(objRow.Row, 6) = "Done"
            End If
        Next objRow

    End With

End Sub

Den Anhang Book1.xls betrachten

Wie gesagt: frag nach wenn Du etwas nicht verstehst oder etwas angepasst haben möchtest.

Ich hoffe geholfen zu haben.

 

Hallo,

erst einmal vielen vielen Dank fuer die Hilfe. Das geht genau in die Richtung die ich haben moechte. zwei kleine Kommentare. Als ich deine Datei heruntergeladen habe, musste ich feststellen dass ich in der Codezeile unten den boolean Wert von false auf true umstellen musste. Das habe ich nicht so ganz verstanden. Wenn man "false" beibehaellt springt er gleich in die msgbox "Arbeitsblatt fuer den MA...." ohne etwas zu kopieren.
PS: ich hab die LOOP variante gewaehlt. Die ist wirklich elegant und macht mich flexibler.

Code:

 
Dim objSheet As Object
Dim bolSheetName As Boolean


    With Me
        
        For lngCounter = 12 To 16
            bolSheetName = [COLOR=#ff0000][U][B]TRUE
[/B][/U][/COLOR]            For Each objSheet In ThisWorkbook.Worksheets
                If objSheet.Name = .Cells(lngCounter, 1).Value Then
                    With objSheet
                        lngRow = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
                        .Cells(lngRow, 1) = Me.Cells(lngCounter, 1)
                        .Cells(lngRow, 2) = Me.Cells(lngCounter, 2)
                        .Cells(lngRow, 3) = Me.Cells(lngCounter, 3)
                        .Cells(lngRow, 4) = Me.Cells(lngCounter, 4)
                        .Cells(lngRow, 5) = Me.Cells(lngCounter, 5)
                        bolSheetName = True
                        Exit For
                    End With
                End If
            Next objSheet
            lngCounter = lngCounter + 1
            If bolSheetName = False Then MsgBox "Das Arbeitsblatt für den Mitarbeiter:" & vbNewLine _
                                                & .Cells(lngCounter, 1) & vbNewLine & "konnte nicht gefunden werden."
        Next lngCounter
        
    End With
    
End Sub

Da mit der true, false Umstellung aber alles wunderbar laeuft bin ich hoch zufrieden. jetzt gibt es noch einen weiteren Punkt der weitere 50% der Formatierungsarbeit erleichtern wuerde.

Eine Integrierte "Delite" funktion im Arbeitsblaat "pool" rundet jetzt alles ab. Parallel zu dir habe ich eine delitantische Moeglichkeit gefunden alles zu kombinieren. Jedoch ist das lang nicht so elegant wie dein Vorschalg bisher. Deswegen wollte ich fragen ob man in den eingebauten Loop nun eine "Delite" funktion einbauen kann. z.B. einen zweiten Loop der die Quellzeile Loescht? Mit dem Loeschen der Quellzeile wird die Liste komprimierter und ich muss nicht manuell die Aufgaben raussuchen die vergeben wurden. Versehentlich koennte man die falsche Zeile mit loeschen.

Es wuerde mich freuen wenn wir diese letzte funktion irgendwie integrieren koennten. Damit waere mir sehr geholfen

herzliche Gruesse

Nico Wilhelm

 

Hallo Nico,

freut mich wenns passt.

Dass mit dem bolschen Wert kann ich mir nicht erklären. Bei mir klappt es wie es soll, und ich hatte auch noch nie ein solches Problem.
Vielleicht kann sich ein anderer Auskenner hier dazu äußern?
Denn wenn Du den Wert auf "true" setzt, ist die ganze Abfrage absurd. Die Messagebox wird niemals erscheinen.



Zu Deiner "Delete-Funktion":
Ich habe das Zellenkopieren erstmal so übernommen wie Du es geschrieben hattest.
Schöner wäre es natürlich, wie in dem anderen Code, nicht einzelne Werte zu übertragen, sondern immer direkt einen ganzen Bereich. Und bei dieser Aktion kann man auch direkt löschen lassen, ist kein Problem.

Dazu bräuchte es aber folgende Infos:
- Kann/darf/soll die ganze Zeile kopiert/gelöscht werden, oder immer nur die 5 Zellen?
- sollen nur die Werte gelöscht, oder die Zellen komplett entfernt werden?
- welche Voraussetzung könnte man dem Makro zum Abfragen anbieten, bevor etwas kopiert/gelöscht wird?
(Ähnlich wie das Wort "Done") Zum Beispiel ein Wert in der 6. Zelle, oder nur wenn alle 5 Zellen ausgefüllt sind... o.ä.

 

Hallo Achim,

Danke fuer deine schnelle Antwort.
Also mit dem true hab ichs auch nicht verstanden. wie gesagt die Funktion wird bei mir nur mit "true" ausgefuehrt. wenn (da wo ich das rote "true" eingetragen habe) ein false eingesetzt wird kommt wirklich bei mir sofort die msgbox. Anyway solange es nur so eine kleinigkeit ist.

zu den Fragen:
Der Sinn dieses Templates soll sein Dass Informationen (einmal eingetragen) dahin bewegt werden wo sie hin muessen.
Ablauf ist ganz klar. Eine Arbeit faellt an, wird verteilt, und dann als erledigt angesehen.
5 Tage lang sammeln wir Aufgaben, 1 mal pro Woche werden die alten Aufgaben verteilt u. 1 mal im Monat werden die erledigten Aufgaben kurz besprochen und dann endgueltig manuell geloescht.

Ich stelle mir nun vor dass der Button im Arbeitsblatt "pool" die Aufgaben verteilt und aus der Liste endgueltig entfernt. Durch das Entfernen schrumpft die liste natuerlich schoen uebersichtlich zusammen (Bedingung nur die, die einen Verwantwortlichen haben, duerfen verschoben und geloescht werden). Im Prinzip eine "Cut and Paste' prozedur. Weil manchmal werden nicht dringende Aufgaben erst in den Folgewochen vergeben. Somit kann es sein dass dauerhaft 5 Aufgaben in der Liste stehen jedoch pro Woche 25 dazu kommen, die aber verteilt werden koennen.

Sind dann die aufgaben abgearbeitet u. durchgestrichen sollen sie genau wie eben ausgeschnitten werden u. ins sheet "done" kopiert werden. Das mag umstaendlich klingen aber die Datei ist auf einem Shareserver abgelegt u. jeder hat nur sein eigenes Sheet bis auf mich. Ich hab mein Arbeitersheet u. das "pool" sheet. Mein Chef hat nur das "Done" sheet u. jeder andere nur sein eigenes. Der fairheit halber kann jeder sehen was der andere macht.

Am Monatsende werden die Aufgaben durchgesprochen und sie werden von meinem Chef endgueltig heraus genommen. Eine Rueckuebertragungsfunktion waere zwar toll steht aber jetzt 0,0 im Vordergrund. Einfach nur das automatische Uebertragen der dutzenden kleinen Aufgaben ist eine ungemeine Arbeitserleichterung.

also zusammengefasst:
zu 1: vorsichtshalber waeren mindestens 8 spalten gut. Ob nun immer die ganze Zeile oder nur ein bruchteil verschoben wird ist letztlich egal. mir kommt es nur auf die info an.
zu 2: am besten ist das komplette entfernen der kopierten zeilen da dann die tabelle zusammen rutscht.
zu 3: Iniziierungsbedingung fuer das kopieren ist die vorraussetzung dass in Spalte "A" der Aufgabe ein Verantwortlicher zugeteilt wurde.

herzliche Gruesse

NW

 

Also für mich ist dies keine Kleinigkeit, ich wüsste schon gerne wieso der Fehler auftaucht.
Denn entweder bin ich zu Blind bei den paar Zeilen, oder es liegt am Versionswechsel, und dann werde ich bald Probleme bekommen, sobald ich halt wechsle (viel schlimmer, wenn meine Firma wechselt...)






Achtung Fehlerwarnung:

Die Zeile:

Code:

lngCounter = lngCounter + 1

ist versehentlich noch im Code (siehe die Beiträge weiter oben).
Sie gehört zu der Schleife:

Code:

lngCounter = 12
Do Until .Cells(lngCounter, 1) = ""
...
Loop

hat aber in der Schleife:

Code:

For lngCounter = 12 To 16
...
Next lngCounter

absolut NICHTS zu suchen!





Ich hoffe mir wird nachgesehen wenn ich ausnahmsweise meine Antwort mal in zwei Posts aufteile,
ich denke es dient der Übersichtlichkeit.

 

Hallo Nico,

die Geschichte mit der Bol-Variable hat sich für diesen Fall wenigstens erledigt, denn die Abfrage wird garnicht mehr benötigt.
Da die kopierten Zeilen ja dann auch direkt gelöscht werden sollen, siehst Du ja was übrig bleibt.
Und wenn im Blatt "Pool" etwas übrig bleibt, dann wurde das passende Blatt halt nicht gefunden.
Das passiert wenn kein Verantwortlicher angegeben wurde (dann soll ja auch nix kopiert/verschoben werden) oder auch wenn das passende Blatt nicht gefunden wurde.
Ich denke diese Arbeitsweise sollte praktikabel für Dich sein.


Auch wenn Dir die "Do - Loop" Schleife besser zu gefallen scheint ;)
habe ich sie wieder durch eine "For - Next" ersetzt.
Aus folgendem Grund:
Wenn die Aufgaben aufgelistet und die Verantwortlichen benannt werden so kann es durchaus vorkommen, dass mitten in der Liste "nicht vergebene" Aufgaben stehen. Und die bisherige "Do - Loop" würde bei der ersten dieser nicht vergebenen anhalten, da die Zelle in Spalte A ja = "" ist.
Ich finde es also sinnvoller von unten die letzte Zeile mit einem Wert in Spalte A zu suchen, und so den Zeilenbereich zu definieren.
Falls als unterstes dann "unvergebene" Aufgaben stehen, werden sie auch direkt von der Schleife ignoriert, was ein wenig die Geschwindigkeit erhöht (minimal).

Im Code-Modul des Blattes "Pool" findest Du zusätzlich zum lauffähigen Code auch noch zwei alternative, aber auskommentierte Versionen der Schleife.
Wenn Du magst kannst Du ja mal alle drei Versionen probieren, vergleichen und Dir eine aussuchen.

Der Unterschied ist folgender:

Code:

Me.Rows(lngCounter).EntireRow.Copy
.Rows(lngRowMA).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Me.Rows(lngCounter).EntireRow.Delete shift:=xlUp

Diese Version halte ich für diesen Fall am sinnvollsten, weil wirklich nur die Werte kopiert werden.

Code:

Me.Rows(lngCounter).EntireRow.Cut .Rows(lngRowMA)
Me.Rows(lngCounter).EntireRow.Delete shift:=xlUp

Dies ist der kürzeste Code, fügt aber auch alle Formatierungen, u.a. auch Deine Drop-Downs mit ein.
Ob das so sinnvoll oder gewollt ist, kannst nur Du entscheiden.

Code:

Me.Rows(lngCounter).EntireRow.Copy Destination:=.Rows(lngRowMA)
Me.Rows(lngCounter).EntireRow.Delete shift:=xlUp

Erzielt das gleiche Ergebnis wie die mittlere Version.


Um die erledigten Aufgaben aus den MA-Blättern nach "Done" zu verschieben, finde ich die zweite oder auch die dritte Version sinnvoll, damit die Formatierung "durchgestrichene Schrift" auch mit übernommen wird.
Andererseits, eigentlich sollte das auch wurscht sein, denn wenn eine Aufgabe auf dem Blatt "Done" auftaucht, dann hat sie jemand als "erledigt" markiert, und sie könnte dort dann auch wieder mit normaler Schrift gut lesbar dargestellt werden. (aber das ist nur mein Gedanke...)


Wichtig ist, existiert das Blatt "Done" nicht, oder ist es abweichend benannt, fährt der Code vor die Wand.
Eine Abfrage ob das Blatt existiert habe ich mir bisher gespart.
Wie groß dieses Risiko ist, und ob die Abfrage sinnvoll/nötig wäre, kannst wiederum nur Du einschätzen.

In Deine Beispieldatei sind nicht alle MA-Blätter gleich, vor allem die Spaltenüberschriften der Aufgaben sind mal in Zeile 11, und mal in Zeile 10.
In Deiner Original-Datei ist das hoffentlich anders.

Mir persönlich würde noch eine Sortierung der Datenzeilen fehlen, und zwar auf jeden Blatt.
Ob nach Priorität, Termin, Erledigungsdatum oder was auch immer.... Deine Entscheidung. ;)

Und wenn wir einmal dabei sind, dann auch gründlich.
Was meinst Du mit:

Den Anhang Book1.xls betrachten


Ich hoffe geholfen zu haben.

 

Hallo Achim,

SUPER! Die erste Loesung ist genau was ich brauche. MENGA MAESSIG VIELEN DANK :) Jetzt macht die Arbeit wieder Spass :)

Mit der Rueckuebertragungsfunktion meinte ich:
Wenn man z.B. versehentlich bei den vielen Aufgaben einmal den falschen Namen zuweist und auf "Aufgabenverteilung klickt ist alles kopiert u. entfernt. Eine linienspezifische "Undo" funktion ware cool.

soetwas in der art nur in funktionierend :)

Code:

Sub UndoLines()
' Variablendeklaration
    Dim s As String
    Dim y1 As Long
    Dim jn As Long
Dim lngRowMA As Long
Dim lngRowDone As Long
Dim lngCounter As Long

' Abfrage nach der Zeile

    s = InputBox("Which line needs to be retransfered", "UNDO LINE")
    If (s <> "") Then 'es wurde nicht "abbruch" oder [x] angeklickt
Sheets("Datenbank").Select
        y1 = Val(s)
        If (y1 > 0) Then 'es wurde ein g黮tiger zahlenwert eingegeben
            jn = MsgBox("Do you want to transfer the line back in the 'Pool' Spreadsheet", vbOKCancel, "Undo?")
            
             If (jn = vbOK) Then
             
' Zurueck kopieren

    With Me
        lngRowMA = .Cells(.Rows.Count, 1).End(xlUp).Row
        For lngCounter = lngRowMA To 4 Step -1
            If .Cells(lngCounter, 1).y1 = True Then
                lngRowDone = ThisWorkbook.Worksheets("Pool").Cells(.Rows.Count, 1).End(xlUp).Row + 1
                .Rows(lngCounter).EntireRow.Copy Destination:=ThisWorkbook.Worksheets("Pool").Rows(lngRowDone)
                Me.Rows(lngCounter).EntireRow.Delete shift:=xlUp
            End If
        Next lngCounter
    End With
End Sub
End Sub

 

 

Hallo Nico,

aus meiner eigenen Erfahrung kann ich Dir dazu:

nur sagen:
Konzentriere Dich bei der Arbeit, dann passiert das nicht.
Eine falsche Auswahl ist schnell passiert, aber bevor ich diese Auswahl bestätige (Button-Klick), sollte ich mir schon sicher sein was ich tue. ;)
Andererseits sind wir alle nur Menschen...

Wenn Du also die Befürchtung hast, die Fehlerhäufigkeit sei so groß, dass manuelle Korrekturen zuviel Aufwand und zuwenig Komfort bedeuten, dann würde ich dafür aber eine möglichst simple und intuitive Arbeitsweise wählen.
Vor allem wenn sich eine Erweiterung der bisherigen Abläufe dafür geradezu anbietet.
Sprich: Think simple.

Damit meine ich, wenn doch eh das "SelectionChange-Ereigniss" und der Zellwert in Spalte A, bzw. die Schriftformatierung benutzt wird, und der Code dafür schon vorhanden ist, kann man dies doch auch so fortführen.


Wobei sich mir zwei Fragen stellen:

1. wie groß sind Deine Listen? Denn "SelectionChange" wird bei jeder Zellauswahl aufgerufen, eventuell läuft das Makro also sehr oft unnütz und kann so die Arbeitsgeschwindigkeit bremsen. Würde es eventuell Sinn machen auf jedem Blatt einen Knopf zum Auslösen des Makros einzubinden?

2. Ich helfe gerne, sonst wäre ich nicht hier. Und es freut mich immer wenn ein Lösungsvorschlag dem Fragesteller hilft, ihn weiterbringt. Der größte Erfolgt ist aber natürlich die Hilfe zur Selbsthilfe. Deshalb auch weiter oben meine Frage ob Du den Code verstehst. Denn im Prinzip ist bereits alles vorhanden, es muss nur variiert werden.
Deshalb nochmal meine Frage: Verstehst Du den Code oder möchtest Du Erklärungen? Wenn ja, was/wo?



Nu aber genug gesabbelt, ab zur Lösung:

Im Blatt "Pool" gehen wir davon aus, der Zellwert in Spalte A ist der entscheidende Faktor.
Deshalb wird die letzte benutzte Zeile über die Spalte A definiert.
Durch die gewünschte "UnDo-Funktion" können auf den MA-Blättern und dem Blatt "Done" aber Zeilen ohne Wert in Spalte A entstehen (verstehst Du weiter unten), deshalb dürfen wir uns im Code dieser Blätter für die Bereichsdefinition nicht mehr auf Spalte A beziehen.
Ich bin nun davon ausgegangen, dass jede Aufgabe eine Task-Nr erhält, und beziehe mich deshalb dort auf Spalte B.

Code:

.Cells(.Rows.Count, [B][COLOR=#ff0000]2[/COLOR][/B]).End(xlUp).Row

Um ein Überschreiben von Daten aber definitiv auszuschließen, habe ich überall folgende Zeile eingefügt:

Code:

.Rows(...).Insert shift:=xlDown

Es mag zwar im Normalfall unnötig sein, eine neue Zeile zwischen die letzte benutzte und die erste leere Zeile einzufügen, aber es bietet halt Sicherheit.

Wurde nun eine Aufgabe dem falschen MA zugeteilt und verschoben, oder hätte sie noch garnicht zugeteilt und verschoben werden sollen, so wechsle auf das Blatt des MA und ändere in der entsprechenden Zeile den Zellwert in Spalte A.
Hier greift dann wieder das "SelectionChange-Ereigniss":
Wurde der Zellwert gelöscht, also keine Zuweisung erteilt, wird die Aufgabe zurück ins Blatt "Pool" verschoben.
Wurde aber ein anderer MA eingetragen, so wird die Aufgabe auf das entsprechende Blatt verschoben.

Genauso funktioniert auch die "UnDo-Funktion" für das Blatt "Done".
Wobei hier zu beachten ist:
Wenn die Zuweisung geändert werden soll, zuerst den Zellwert in Spalte A ändern, dann erst die Schriftformatierung der Zeile auf "nicht durchgestrichen" setzen.
Ändert sich nichts an der Zuständigkeit, reicht es die Schriftformatierung zu ändern.

Thats it.



Zusätzlich noch einige Vorschläge (abgesehen von der Sortierung):

- wenn die Verantwortlichkeit nachträglich geändert werden kann, könnte es hilfreich sein auf den MA-Blättern und dem Blatt "Done" auch die Drop-Downs für die MA-Auswahl zu haben.
Dafür müssten auf diesen Blättern die MAs genauso aufgelistet werden wie im Blatt "Pool" und die Kopiermethode müsste auf

Code:

Me.Rows(lngCounter).EntireRow.Cut .Rows(lngRowMA)

geändert werden. Denn dann werden die Drop-Downs mit übertragen und ziehen sich die Werte automatisch vom jeweiligen Blatt (getestet).

- wiederum je nachdem wie groß Deine Listen sind, könnte es für den User angenehmer und für die Performance sinnvoll sein, die Bildschirmaktualisierung zeitweise auszuschalten:

Code:

Application.ScreenUpdating = False

Und da das Verschieben der Zeilen auf ein anderes Blatt auch das jeweilige "SelectionChange-Ereigniss" auslöst, könnte man dies ebenfalls zeitweise ausschalten:

Code:

Application.EnableEvents = False

Allerdings ist bei beidem Vorsicht geboten!
- es darf nie vergessen werden auch wieder anzuschalten was man ausgeschaltet hat
- bei einem Makroabbruch wird die Zeile zum anschalten eventuell niemals erreicht, also ist eine Fehlerbehandlung unbedingt zu empfehlen, die im Fall der Fälle wenigstens wieder anschaltet was ausgeschaltet wurde
- um nicht in userspezifischen Einstellungen rumzupfuschen sollten die entsprechenden Eigenschaften generell zuerst ausgelesen und die Werte an Variablen übergeben werden, um so später den Originalzustand wieder herstellen zu können


Den Anhang Book1.xls betrachten


Ich hoffe geholfen zu haben.

 

Hallo Achim,

wieder einmal verbluefst du mich. Die Erklaerung habe ich verstanden. Ich verstehe auch mittlerweile die Codezeilen. Was mir fehlt ist das verstaendniss der Syntax um einen Code selber zu schreiben. Ich verstehe z.b. dass verschiedene Informationen durch den "Punkt" getrennt werden. Jedoch wenn ich die Reihenfolge betrachte habe ich grosse Probleme bei komplexen Faellen den Code anzulegen. Ich habe deinen Code zwei mal lesen muessen und dann konnte ich gedanklich den Zeilen folgen wie sie wann, wohin verschoben werden. Die Erklaerungen dazu waren dringend notwendig. Ansonsten haette ich deine Zeilen nicht verstanden.

Oben hast du geschrieben think simple und benutze was du bereits hast. Habe ich deinen Code richtig verstanden indem ich sage:
Die entscheidende Stelle ist das "ElseIf Not"?
Danach wird die Abfrage "quasi" Rueckwaerts definiert. "lngRowPool" u. "lngRowDone" sind meine Zielzeilen die ich benennen muss damit meine Objektposition leichter im makro benutzt werden kann. Oder? dann definierst du erst einmal die Position ueber worksheet, etc.... und als letztes kommt dann immer der auszufuehrende Befehl. Wenn ich das richtig sehe.

Mein Problem beim selber schreiben ist dass mir meistens wirklich das Verstaendnis fuer die Syntax fehlt um meine Objekte zu manipulieren. Z.B. wenn ich Werte abgleichen will oder einen bestimmten Zellwert als referenz verwenden moechte. Wenn ich die Codes lese ist es absolut klar was gerade manipuliert wird. nur habe ich irgendwie zu wenig uebung um diese ganzen kuerzel zu verstehen z.b. wie das "lng" was vor vielen Woertern steht.

Mit diesem Kopiermakro konnte ich aber viel Lernen. Darueber bin ich sehr dankbar. Ich denke mit etwas mehr uebung kommt dann auch die Schreibroutine. Wenn ich mir ueberlege dass ich vor einem Jahr noch nicht einmal den Makro rekorder verwenden konnte bin ich jetzt schon viel besser unterwegs. Und das nur durch Menschen wie dich, die sich Zeit fuer mich nehmen.
Hut ab Achim und vielen Dank fuer deine Muehen. Sie sind nicht verschwendet. Somit wuensche ich dir ein tolles Wochenende und ich hoffe dass du auch weiterhin Leute so toll helfen kannst. Das freut mich wirklich.
Jetzt folgen noch formatierungsarbeiten u. die Uebertragung der Codezeilen in die original Date und ab naechster Woche wird dann damit gearbeitet.

Herzliche Gruesse

Nico

 

Hallo Nico,

ich hoffe Du schaust nochmal rein und liest dies hier.
Damit Du den Code besser nachvollziehen kannst, versuche ich mal ein paar Dinge zu erklären.
Vieles ist auch im Netz zu finden, und dort auch kompetenter erklärt als ich es vermag.
(wenn jemand Fehler oder Unklarheiten findet darf er/sie mich natürlich gerne verbessern)


Fangen wir mit dem "Kopiermakro" oben im Code an:
"Option Explicit" sollte immer am Anfang jedes Code-Modules stehen.
Über "Extras - Optionen - Variablendeklaration erforderlich" kann man im Makro-Editor einstellen, dass dieser Befehl automatisch gesetzt wird.
Dieser Befehl "zwingt" den User seine Variablen zu deklarieren ("Dim") und bietet noch andere Vorteile. Zu dem Thema wirst Du im Netz gut fündig.

"Dim" ist eine Form der Variablendeklaration. Es gibt auch noch andere Derklarationsarten wie "Public" oder "Static".
Auch dazu ist im Netz einiges zu finden.

Wird eine Variable deklariert, sagst Du Excel damit nicht nur: "Guck her, diese Variable gibt es jetzt und ich will sie benutzen."
Du sagst Excel auch zusätzlich von welchem Typ diese Variable ist (Text, Long, Object ...).
Dadurch weiß Excel wie es mit der Variable arbeiten kann und soll. (vereinfacht gesagt)
Damit Du, und auch jeder andere der Deinen Code liest, aber ebenfalls weiß von welchem Typ diese Variable ist, sollte man ein Präfix vor den Namen stellen, z.Bsp. "lng" für den Typ "Long".
Dies findest Du zum Bsp. hier
http://de.wikibooks.org/wiki/VBA_in_Excel/_Namenskonventionen
näher erklärt.

Variablen sollten möglichst "sprechende" Namen haben, damit man auch mitten im Code schnell erkennt worum es gerade geht.
Was sagt uns also folgender Variablenname: "lngRowPool"
"lng" = Typ Long
"Row" = es geht um Zeilen
"Pool" = es geht um das Blatt "Pool"



Die Geschichte mit den Punkten ...
(ist für mich schwierig zu erklären und eine gute Seite habe ich auf die Schnelle auch nicht gefunden)
Vereinfacht gesagt sagst Du Excel damit welches Objekt, bzw. welche Eigenschaft von welchem Objekt Du ansprechen willst.
Beziehst Du Dich auf die aktuelle Mappe schreibst Du "ThisWorkbook".
Beziehst Du Dich aber auf das erste Arbeitsblatt der aktuellen Mappe, schreibst Du "ThisWorkbook.Worksheets(1)".
Und nun die Zelle "A1" im ersten Blatt der aktuellen Mappe: "ThisWorkbook.Worksheets(1).Range("A1")"
(Für Zellen und Bereiche gibt es mehrere Schreibweisen die alle ihre Vor- und Nachteile haben)
Genauso kannst Du auch Eigenschaften ansprechen: "objSheet.Name" = der Name des Blattes, welches gerade durch die Variable "objSheet" benannt wird
Ich hoffe Du verstehst das Prinzip?

Die Anweisung "With" vereinfacht die Referenzierung.
Anstatt in jeder Zeile und vor jedem Objekt "ThisWorkbook" zu schreiben, kannst Du auch
"With ThisWorkbook" und "End With" benutzen.
Alles was dazwischen steht wird nun automatisch auf "ThisWorkbook" bezogen, wenn ein "." vorangestellt wird wie bei ".Worksheets(1)", und nicht eine andere Referenz wie "Workbooks("XY").Worksheets(1)" stattdessen angegeben wird.
So kann man sich viel Schreibarbeit sparen, der Code wird übersichtlicher und kürzer, und durch die Gliederung kann man auch ganz gut trennen was gerade wo passieren soll.

Das "Me" ist quasi eine Abkürzung der Referenz.
Im Code-Modul eines Objektes (Arbeitsblatt, Userform ...) kann man mit "Me" immer den Bezug zu diesem Objekt setzen. Kurz - übersichtlich - eindeutig - und, manchmal auch angenehm variable, wenn wie in Deinem Fall einmal Code geschrieben und dann in mehrere Module von anderen Blättern kopiert wird. Man muss nicht explizit das entsprechende Blatt jedesmal ansprechen, das "Me" bezieht sich immer auf das Blatt in dessen Modul es benutzt wird.



Mit der Zeile

Code:

lngRowPool = .Cells(.Rows.Count, [COLOR=#ff0000]1[/COLOR]).End(xlUp).Row

wird die unterste Zeile ermittelt, in der in Spalte 1 ein Wert steht, und diese Zeilennummer wird an die Variable übergeben.
Damit können wir also das Ende eines Bereiches definieren, damit z.Bsp. eine Schleife nicht völlig unnötig ins Leere läuft.


Die Zeile

Code:

For lngCounter = lngRowPool To 12 Step -1

ist ein Schleifenkopf (erste, initiierende und auch definierende Zeile einer Schleife).
Die Syntax lautet: For - To - Step (Von - Bis - Schrittweite)
"lngCounter" ist eine Variable die als Schleifenzähler benutzt wird.
Der Startwert wird durch "= lngRowPool" definiert, in diesem Fall also die oben genannte letzte Zeile die wir uns haben raussuchen lassen.
Die 12 gibt hier den Endwert der Schleife an. Erreicht der Schleifenzähler "lngCounter" diesen Wert, wird diese Schleife zum letzten Mal durchlaufen und danach verlassen.
"Step -1" gibt vor, das der Schleifenzähler in Einser-Schritten rückwärts (absteigend) gezählt wird.
In diesem Fall von der letzten Zeile bis zur 12. Zeile.
Rückwärts ist in diesem Fall sinnvoll, da ja komplette Zeilen gelöscht oder hinzugefügt werden.
Dadurch verschieben sich in Bezug auf die einzelnen Zellwerte die Zeilennummern, und der Code kann zu unerwarteten Ergebnissen führen, oder sogar endlos vor sich hin laufen.
Deshalb rückwärts zählen, damit alle Änderungen "hinter der Schleife" passieren und nicht "vor ihr", sprich die Änderungen passieren dort, wo der Schleifenzähler bereits war. Es hat keinerlei Auswirkung mehr auf die Schleife.
Am Ende der Schleife folgt der Schleifenfuss

Code:

Next lngCounter

Damit wird der Schleifenzähler entsprechend der oben angegebenen Schrittweite weiter gezählt und löst so den nächsten Durchlauf aus.
Alles was zwischen Schleifenkopf und Schleifenfuss steht ist der Schleifenkörper.
Dorthinein packt man alle Aufgaben, die diese Schleife halt ausführen soll.

Nicht alles von dem geschriebenen gilt für alle Schleifenarten. Es gibt sehr unterschiedliche Typen, die man sich erst genau anschauen und verstanden haben sollte, bevor man sie verwendet.
Dazu gibt es aber auch haufenweise Erklärungen im Netz.


Die Zeile

Code:

For Each objSheet In ThisWorkbook.Worksheets

ist wieder ein Schleifenkopf. Es ist also eine Schleife innerhalb der ersten Schleife.
"For Each" benutzt man bei Objekten. Damit wird jedes Objekt in der angegebenen Objektsammlung durchlaufen.
"ThisWorkbook.Worksheets" bezeichnet diese Objektsammlung und meint: "alle Arbeitsblätter in dieser Arbeitsmappe".

Somit wird durch die äußere Schleife jede Zeile des angegebenen Bereiches durchlaufen, und durch die innere Schleife für jede dieser Zeilen jedes Arbeitsblatt.
Bei 10 Zeilen und 10 Arbeitsblättern würden die Arbeitsblätter also 10x durchlaufen, es ergäben sich also insgesamt 100 Schleifendurchläufe dir inneren Schleife.


Die Zeile

Code:

If objSheet.Name = .Cells(lngCounter, 1) Then

ist eine einfache Abfrage nach einer Bedingung: wenn Bedingung xy erfüllt - dann tue dies
Ist die Bedingung nicht erfüllt, wird auch nichts getan, solange nicht weitere Abfragen für diesen Fall eingebaut sind.
Mit "Else" (ansonsten) erreicht man, dass in jedem Fall, in dem die erste Bedingung nicht erfüllt wird etwas bestimmtes getan wird.
Mit "ElseIf" (ansonsten - wenn) kann man eine zweite Bedingungsabfrage einbauen.
Wird die erste Bed. nicht erfüllt, wird jetzt geprüft ob die zweite erfüllt ist.
Geprüft wird übrigens, ob der Blattname mit dem Zellwert identisch ist.

Damit die innere der beiden o.g. Schleifen nicht unnötig lange läuft, habe ich die Zeile

Code:

Exit For

eingebaut. Sie wird ausgeführt wenn die Bedingung der "If-Abfrage" erfüllt wurde, also das passende Blatt zum MA gefunden wurde. In dem Moment ist die Aufgabe erledigt, die Blätter müssen nicht weiter geprüft werden, und es kann zur nächsten Zeile im Blatt "Pool" übergegangen werden.
Diese Zeile beendet die nächstliegende "For-Schleife", in diesem Fall also die innere der beiden.


Die Zeile

Code:

With objSheet

ist die variable Referenzierung auf das Arbeitsblatt, welches die Variable "objSheet" gerade repräsentiert.


Die Zeile

Code:

.Rows(lngRowMA).Insert shift:=xlDown

fügt eine Zeile ein (insert) und schiebt alle anderen nach unten (shift:=xlDown).
Der Ort wird durch ".Rows(lngRowMA)" definiert, hier diesmal eine Zeile unter der ermittelten letzten Zeile.


Die Zeile

Code:

Me.Rows(lngCounter).EntireRow.Copy

kopiert eine komplette Zeile (EntireRow).
Die Referenzierung wo das passieren soll, kannst Du inzwischen hoffentlich selber entschlüssen?


Die Zeile

Code:

.Rows(lngRowMA).Paste

würde einfach nur den momentanen Inhalt der Zwischenablage am angegebenen Ort einfügen.
Durch "PasteSpecial" hat man die Möglichkeit die Art des Einfügens zu beeinflussen.
Z. Bsp. mit "Paste:=xlPasteValues" = nur Werte einfügen


Die Zeile

Code:

Me.Rows(lngCounter).EntireRow.Delete shift:=xlUp

(löschen) sollte sich Dir ja nun auch in ihren Einzelheiten erschließen, oder?


Gehen wir nun zu den MA-Blättern:

Code:

Option Explicit


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim lngRowPool As Long
Dim lngRowMA As Long
Dim lngRowMA2 As Long
Dim lngRowDone As Long
Dim lngCounter As Long
Dim objSheet As Object

    With Me
        lngRowMA = .Cells(.Rows.Count, 2).End(xlUp).Row
        For lngCounter = lngRowMA To 12 Step -1

Bis hierhin sollte ja nun alles für Dich klar sein (wenn ich gut genug erklärt habe).


Die Zeile

Code:

If .Cells(lngCounter, 1).Font.Strikethrough = True Then

prüft, ob die Schriftformatierung (Font) auf durchgestrichen (Strikethrough) steht.
Hier kannst Du auch wieder schön die Hierarchie der Referenzierung verfolgen:
"." referenziert auf "With Me", also in dem Fall ein Arbeitsblatt.
Also ist ".Cells(lngCounter, 1)" die Zelle mit der Zeilennummer "lngCounter" und der Spaltennummer 1 in eben jenem Arbeitsblatt.
Und nun wird sich auf eine Eigenschaft eben jener Zelle bezogen ".Font" und dann noch zusätzlich auf eine Einstellung/einen Wert ".Strikethrough" eben jener Eigenschaft von eben jener Zelle auf eben jenem Arbeitsblatt.
Du siehst, eigentlich eine einfache Verkettung.


Die Zeile

Code:

.Rows(lngCounter).EntireRow.Copy Destination:=ThisWorkbook.Worksheets("Done").Rows(lngRowDone)

ist eine andere Schreibweise für "copy-paste" als wir sie oben bereits hatten.
Sie arbeitet aber auch anders als die erste.
Es ist ein Einzeiliger-Befehl. Etwas wird kopiert und direkt in den angegebenen Zielbereich (Destination) eingefügt, und zwar komplett, mit allem drum und dran. Also auch Formatierungen usw.
Mir ist keine Möglichkeit bekannt hier irgendwelche Einstellungen vorzunehmen, oder zwischen "copy" und "paste" noch andere Aktionen ausführen zu lassen.
Bei der ersten Variante geht dies hingegen beides.
Je nach Aufgabenstellung kann mal die eine und mal die andere vorteilhaft sein.

Code:

ElseIf Not .Cells(lngCounter, 1) = Me.Name Then

Wenn nun die erste Bedingung (durchgestrichene Schrift) nicht erfüllt ist, dann (und nur dann) wird durch das "ElseIf" die zweite Bedingung überprüft.
In diesem Fall wäre sie erfüllt wenn der Zellwert nicht (Not) dem Blattnamen (MA-Namen) entspricht, wenn also die Zuständigkeit geändert wurde.
In dem Fall kommen wir zu einer weiteren Abfrage (die Verschachtelung entspricht in etwa der Verschachtelten Wenn-Formel).
Jetzt wird geprüft ob überhaupt ein Zellwert eingegeben wurde ("" bedeutet "kein Wert", nicht zu verwechseln mit "unbenutzt" bei "UsedRange" und manchmal auch nicht gleich "leer").
Wurde kein Zellwert angegeben, ab mit der zeile zurück in den Pool.
Ansonsten "Else" wieder die Schleife durch alle Blattnamen um den passenden Verantwortlichen zu suchen.


Im Blatt "Done" ist es das gleiche Prinzip.

Ich hoffe das Du den Code nun besser verstehst, und vielleicht auch das eine oder andere bei fremdem Code besser entschlüsseln kannst.
Sollten trotzdem noch Fragen offen sein, Du weißt ja wo Du geholfen wirst ...
;)

 

Hi Achim,

also ich habe mir wie immer gerade 2 mal deinen Post durchgelesen und hab jetzt alles verstanden worum es in unserem Code ging. Die Erklaerung ist genau das was mir immer gefehlt hat. Danke dass du dir die unglaubliche Zeit genommen hast, mir erstens zu helfen und nun auch noch mir Stueck fuer Stueck zu erklaeren was passiert u. wie es darzusetellen ist. Ich stelle gerade fest dass sich hinter den Codezeilen doch nicht so viel Hexenwerk steckt, wie urspruenglich immer angenommen. Die "Sprache" ist wirklich sehr direkt u. man muss sich wie in der Mathematik einfach nur an Regeln der Darstellung halten. Sobald alle Variablen deklariert sind kann eigentlich nicht mehr viel schief gehen. Ich habe mir jetzt zum Ziel gemacht jede Woche ein kleines Makro selber zu schreiben. Ich hoffe es wird mir gelingen um einfach in der uebung zu bleiben.

Achim, noch einmal vielen Herzlichen Dank. Die Erklaerungen waren wirklich super und ich speichere alles wie gewohnt ab. Mit dem letzten Post hast du mir bei einem Risenschritt der Selbsthilfe geholfen. Danke.

Eine Frage stellt sich mir allerdings noch. Die ist jedoch sehr allgemein. Bei Fehlermeldungen, bekomme ich oft entweder einen Laufzeilenfehler '1024' glaube ich oder einen object missing Fehler. Was fuer eine Methodik sollte ich anwenden ich mit solchen Fehlern konfrontiert bin. Wie geht man vor um den Fehler zu identifizieren wenn er nicht gelb unterlegt ist?

Liebe Gruesse Nico

 

Hallo Nico,

eine Methodik für den Umgang mit Fehlermeldungen kann ich Dir leider nicht bieten, dafür ist mein Wissen zu begrenzt.
Vielleicht kann sich ja einer der anderen (wirklichen) Cracks dazu äußern?

Ich kann Dir nur sagen was ich selber mache:
- zuallererst nach Tippfehlern schauen
- schauen ob ich auch die richtigen Objekte/Eigenschaften/Methoden anspreche
- schauen ob auch das richtige Objekt (Mappe, Blatt, UserForm, Button...) gerade aktiv ist
- schauen ob nicht gerade ein Blatt- oder Arbeitsmappenschutz aktiv ist
- Infos über die Fehlermeldung im Netz suchen (kann manchmal echt hilfreich sein)
- wegen Betriebsblindheit irgendwann aufgeben und auf den nächsten Tag verschieben

und vor allem das allerwichtigste: das Makro im Einzelschrittmodus laufen lassen
Dazu verkleinere ich mir meist die beiden Fenster der Mappe und des Editors so, dass ich sie nebeneinander sehen kann, setze mir einen Haltepunkt im Code, lasse den Code bis zum Haltepunkt laufen und verfolge ab dann schrittweise was er macht (sollte man eh immer tun, sonst programmiert man blind).
So fällt mir dann meist recht schnell mein Fehler auf.

 

Chapeau!