Clever-Excel-Forum

Hallo zusammen,

ich möchte eine monatliche Übersicht über die anstehenden Lohnzahlungen erstellen, die sich automatisch generiert. Basis hierfür soll eine Liste im ersten Tabellenblatt sein, in der alle Mitarbeiter mit Eintritts- und Austrittsdatum erfasst werden (siehe Dateianhang). Aus dieser Liste sollen alle relevanten Datensätze automatisch in weitere Tabellenblätter (Januar-Dezember) übernommen werden, damit man die Mitarbeiter nicht immer manuell in jedes Tabellenblatt kopieren muss. Gleichzeitig sollen die Lohnübersichten, die in der Vergangenheit liegen nicht verändert werden.

Beispiel:
Mitarbeiter 1 wird am 1. März 2019 eingestellt und verlässt am 30. Juni 2019 die Firma wieder. Folglich muss er in den Tabellenblättern "März" bis "Juni" in den jeweiligen Tabellen aufgelistet werden, in den Tabellenblättern "Januar", "Februar" und "Juli" bis "Dezember" jedoch nicht.

Aktuell habe ich die Abfrage, in welchem Tabellenblatt der Mitarbeiter erfasst werden soll, über eine "WENN"-Funktion abgefragt. Dadurch entstehen jedoch in den Tabellen (z. B. Januar) Leerzeilen an der Stelle an der die Mitarbeiter stehen, die zu diesem Zeitpunkt nicht für die Firma tätig sind.

Meine Fragen:

1. Lässt sich das eventuell etwas einfacher und besser lösen als mit der "WENN"-Funktion?
   
2. Falls nicht, ist es möglich, die Tabellen ohne Filterfunktion von den leeren Datensätzen automatisch zu bereinigen?
   

Ich hoffe, ich habe mich verständlich ausgedrückt. Sollte es noch offene Fragen geben beantworte ich diese natürlich sehr gerne :)

Danke schon mal für eure Hilfe.

LG
Eva

Hi,

trage alle Daten in dein Tabellenblatt "Mitarbeiterliste" ein. Dann kannst du mit Pivot nach Monaten auswerten.

Hallo,
hier eine Lösung mit VBA.

Hallo zusammen,

vielen Dank schonmal für eure Vorschläge. Der VBA Ansatz gefällt mir schonmal ganz gut.

Gibt es auch die Möglichkeit, dass nur die Liste des aktuellen Monats erstellt wird und die anderen Listen erst mal frei bleiben sowie die Listen, die schon erstellt wurden nicht mehr angerührt werden?

Ich werde noch eine Schaltfläche einfügen mit der man mit einem Klick alle Daten im betroffenen Tabellenblatt löschen kann, dann geht das Leeren der Zellen schneller und unkomplizierter.

Vielen Dank euch!

LG

Hallo,
hier eine Neue Variante.
Wichtig! Wenn weitere Tabellen NEU hinzukommen muss der Code angepasst werden! Sonst Löscht er diese mit.

Hallo Armin,

vielen Dank für deine schnelle Antwort. Du hast mir bisher schon sehr geholfen!

Wäre es möglich nach der generierung der Daten, die Tabellenblätter mit einem dynamischen Tabellendesign zu formatieren? Etwa sowas wie:

---

Range("B3").Activate
With ActiveCell.CurrentRegion
.Borders.LineStyle = xlContinuous
End With

---

Leider bin ich in VBA noch sehr weit am Anfang, daher habe ich es nicht geschafft, den Code an der Richtigen Stelle in deinem zu integrieren.
Es sollten eigentlich keine neuen Tabellenblätter mehr hinzukommen. Das Tabellenblatt "Monat_1_x" wird von meiner Seite aus auch nicht mehr benötigt.

Kann ich ohne Bedenken in der Mitarbeiterliste 2 weitere Spalten (Zuschläge, 30% Steuer) einfügen und die Gesamtkosten per Formel automatisch berechnen lassen?

Vielen vielen Dank!

Hallo Eva,
so jetzt habe ich Dir das noch eingebaut. Hoffentlich habe ich nichts übersehen.
Das Datum in den Monatstabellen A1 wird automatisch gesetzt. Damit das Programm weis ob Die Tabelle bearbeitet wurde.

Hi Armin,

das ist echt genau so wie ich es mir vorgestellt habe. Eine Sache noch (habe die aktuelle und ein bisschen von mir abgeänderte Version angehängt).

Ich habe im Haupttabellenblatt "Mitarbeiterliste" jetzt noch die Spalten "Zuschlag" und "30% Steuer" miteingefügt und den Code versucht wie folgt abzuändern.

---

For Each Rx In WS.Range("B4:B" & N)

    If IsDate(Rx.Offset(0, 6).Value) Then EndDat = Rx.Offset(0, 6).Value Else EndDat = CDate("31.12." & WS.Range("A1").Value)
    If IsDate(Rx.Offset(0, 5).Value) Then
       AnfDat = Rx.Offset(0, 5).Value
    Else
       Exit For
    End If
    For K = 0 To DateDiff("m", AnfDat, EndDat)
        Tx = Format(DateAdd("m", K, AnfDat), "MMMM"
      With Worksheets(Tx)

            If Not IsDate(.Cells(1, 1).Value) Then
                 M = .Cells(Rows.Count, 1).End(xlUp).Row + 1
                .Cells(M, 1).Value = Rx.Value
                .Cells(M, 2).Value = Rx.Offset(0, 1).Value
                .Cells(M, 3).Value = Rx.Offset(0, 2).Value
                .Cells(M, 4).Value = Rx.Offset(0, 3).Value
                .Cells(M, 5).Value = Rx.Offset(0, 4).Value
                Formatieren M, Tx
             End If
       End With

---

Allerdings funktioniert das nur dann, wenn ich alle Tabellenblätter aktualisiere. Kannst du dir das eventuell nochmal durchschauen?


In Spalte 5 ("Gesamtkosten") des Tabellenblattes (z. B. Januar) in das die Werte kopiert werden , solll eine Summenformel eingefügt werden, die sich je nach Zeile ändert.

Für Zeile 4
"=SUMME(B4:E4)"

Für Zeile 5
"=SUMME(B5:E5)"

etc. und je nach dem wie viele Datensätze vorhanden sind. Also wenn im Januar 6 Mitarbeiter gelistet sind soll in 6 Zellen die Summenformel stehen und die Summe berechnet werden.

Sorry, dass das jetzt erst kommt. Danach sollte alles erledigt sein. Riesen Dank für deine Hilfe! Du bist großartig!

LG

Guten Morgen Eva,
habe Deinen Wunsch realisiert. Hoffe das wir jetzt die Sache im Griff haben.
Viel Erfolg!

Hallo Armin,

Du bist super. Vielen vielen Dank!!
Ein bisschen was lernen konnte ich auch, obwohl ich natürlich noch weit von dem entfernt bin, was du kannst...

LG