Sortieren über mehrere Tabellenblätter

[kafi112]

Moin.
Ich hätte da mal wieder ein Problem mit Excel 2016.
In meiner Excel Mappe ist auf dem ersten Blatt eine Liste, in der alle Mitarbeiter eingetragen werden.
In den Blättern "Januar" bis "Dezember" gibt es für jeden Mitarbeiter und für jeden Tag ein Dropdown Menü.
In den Spalten "AF" bis "AI" wird die Summe der einzelnen Einträge je Mitarbeiter/Monat addiert.
Auf dem Tabellenblatt "Mitarbeiter" sollen alle Einträge pro Mitarbeiter/Jahr angezeigt werden.
Die Mappe habe ich soweit erstellt und auch die Summenformeln, bzw. die Formatierungen sind nicht das Problem.

Ich möchte nun erreichen, dass die Mitarbeiter vom Blatt "Mitarbeiter" automatisch in die Blätter "Januar" bis "Dezember" übernommen werden.
Zusätzlich, wenn ich einen neuen Mitarbeiter am Ende der Liste hinzufüge, möchte ich die Mitarbeiter nach Nachname sortieren können. Natürlich auf alle Blätter und so, das die bereits gemachten Einträge und Summen dem richtigen Mitarbeiter weiter zugeordnet bleiben, also mit sortiert werden.

Ich hatte mir dazu auf dem ersten Blatt bereits eine Schaltfläche eingefügt, ein Makro aufgezeichnet und dieses der Schaltfläche zugeordnet.
Leider hat das so nicht funktioniert.
Das Makro sah in etwa so aus:
"Mitarbeiter" Blattschutz aufheben - Zeile 2 (A-F) filtern - Spalte A von A-Z sortieren - Zeile 2 Filter deaktivieren - "Mitarbeiter" Blatt schützen - "Januar" Blattschutz aufhebben - Zeile 2 (A-AG) filtern - Spalte A von A-Z sortieren - Zeile 2 Filter deaktivieren - "Januar" Blatt schützen usw. usw.

Ich hoffe ihr könnt mir da weiterhelfen.
Zur besseren Veranschaulichung, habe ich die entsprechende Datei hier mal angehängt.

Vielen Dank schon mal im Vorraus.

Gruß
kafi112

 

[Scientist]

Mit dem Makrorekorder wird das nicht funktionieren, da wirst du selbst in VBA handanlegen muessen.
Fuer ein rudimentaeres Skript brauch es an sich auch nicht viel (for-Schleife, letzte Zeile ermitteln/errechnen, Range.copy, Range.sort).
Man muesste sich jetzt noch Gedanken machen, wann das Makro ausgefuehrt wird, wie unbekannte Eintraege ermittelt werden und wie man Namensaenderungen vornehmen kann (um Tippfehler, Namensaenderungen, usw. zu beruecksichtigen)

Oder du lebst damit, dass es unsortiert ist, dann brauchst du natuerlich nichts von all dem.

 

[GunMum]

Moin,
wenn ich die Beschreibung richtig interpretiere sollte das über Daten | Abrufen und transformieren aka Power Query machbar sein. Da ich keine fremden VBA-behafteten Files öffne, kann ich das nicht ausprobieren.

 

[kafi112]

Mit dem Makrorekorder wird das nicht funktionieren, da wirst du selbst in VBA handanlegen muessen.
Fuer ein rudimentaeres Skript brauch es an sich auch nicht viel (for-Schleife, letzte Zeile ermitteln/errechnen, Range.copy, Range.sort).
Man muesste sich jetzt noch Gedanken machen, wann das Makro ausgefuehrt wird, wie unbekannte Eintraege ermittelt werden und wie man Namensaenderungen vornehmen kann (um Tippfehler, Namensaenderungen, usw. zu beruecksichtigen)

Oder du lebst damit, dass es unsortiert ist, dann brauchst du natuerlich nichts von all dem.

Und das übersteigt leider meine Excel Kenntnisse.
Ich könnte natürlich auch das erste Blatt weglassen und jeden neuen Mitarbeiter auf jedem Blatt eintragen.
Dann ist das Sortieren ja auch kein Problem.
Das ist aber nicht das was ich möchte.

Im Anhang die Datei nochmal ohne Makro, obwohl das im 1. Beitrag auch schon gelöscht war.
Vielleicht magst du ja da mal einen Blick drauf werfen.

 

[GunMum]

OK, es geht (bedingt) mit Power Query.
Aber ich weiß, dass du damit nicht glücklich werden kannst.
Ich sage es einmal vorsichtig: Dien Plan ist einfach nicht so, dass EXCEL gut damit klar kommt. Der MENSCH sehr wohl, wegen der vielen, netten PullDowns.
Ich glaube, dass du semiprofessionell mit VBA zum Ziel kommst, professionell wäre beispielsweise Access. Damit ließen sich gewiss all deine Wünsche erfüllen.

Und nur damit du siehst, dass es prinzipiell möglich ist, habe ich meinen Entwurf angehängt. Die ersten 4 Monate in der Kurzschreibweise sind von mir. Und wenn du magst, darfst du dich da gerne einarbeiten, für viele Zwecke ist das Power Query eine hervorragende Lösung!

 

[Surtia]

Es müsste noch geklärt werden unter welchen Bedingungen ein Mitarbeiter zu welchem Monat zugeordnet werden soll. Wie legst du diese im Makro fest? Abgesehen davon sollten die Punkte von Scientist beachtet werden. Was tust du, wenn ein Name korrigiert werden muss?

Wenn du einen Namen unter Mitarbeiter einträgst, kannst du ihn innerhalb von Sekunden zu anderen Monaten kopieren. Wie du selbst erwähnst ist das Sortieren manuell (in Form ein Tabelle oder Filter) sofort und schnell möglich. Da es sich um eine Jahresübersicht handelt vermute ich, dass die Änderungen zeitlich gering ausfallen.

Ich vermute mit VBA machst du Dir für diesen komplexen Fall mehr Arbeit. Natürlich kann ich mich auch irren.

Kleiner Tipp:
Das exta Registerblatt "Tabelle2" könntest Du löschen.
Statt in der "Datenüberprüfung" unter "Liste" einen Brereich anzugeben kannst Du auch direkt "Dienst statt Ruhe;Ruhe statt Dienst;DT MA;DT Dispo" eingeben. Ist etwas sauberer.

 

[Scientist]

Ich vermute mit VBA machst du Dir für diesen komplexen Fall mehr Arbeit. Natürlich kann ich mich auch irren.

Als komplex wuerde diese Aufgabe nicht bezeichnen ...
Nach dem ich noch mal darueber nachgedacht habe, muss ich auch meine Aussage etwas revidieren.
Ich denke, es ist mit gewissen einschraenken doch moeglich, diese Aufgabe mit dem Makrorekorder zu loesen.
Es ist nur nicht sehr schick, wuerde aber gehen.
Man muss nur wissen, wie man die schwaechen des Makrorekorders umschiffen kann.

Ein eigenes Skript zu erstellen scheint ja keine Option zu sein.
Also hier mein Vorschlag zur Loesung mit dem Makrorekorder:

1)    "Eingabemaske erstellen"
      Ein definierter Bereich, in den die Daten stehen, die kopiert werden sollen.
2)    Makrorekorder starten
3)    Daten aus der Eingabemaske kopieren (so viele aufeinmal wie moeglich)
4)    Daten in das erste Blatt einfügen, aber an ein selbstgewaehltes untere Ende der Tabelle
      Bspw. Zeile 50 bei max (49 - Überschrift) Eintraegen
5)    3) und 4) wiederholen, bis alle Daten uebertragen wurden
6)    komplette Tabelle im ersten Blatt markieren,
      so dass der komplette Bereich abgedeckt waere,
      auch wenn die Eingabemaske vollgeschrieben ist.
7)    markierte Tabelle nach Wunsch sortieren
      (ich persoenlich wuerde ueber "Rechklick" -> "sortieren" arbeiten)
8)    1) bis 7) mit den anderen Blättern wiederholen.
9)    Daten in der Eingabemaske entfernen
10)   Aufnahme beenden
11)   aufgenommenes Makro auf einen Button legen

Namen muessten aber aufgrund des Blattschutzes haendisch korrigiert werden.
Sonst haette man mit Suchen und Ersetzen arbeiten koennen.
Eine Loesung mit dem Makrorekorder faellt mir spontan nicht ein

 

[kafi112]

Erstmal Danke für eure Anregungen.
Heute wird mir das zu spät und ich werde mich morgen weiter damit beschäftigen.

2 Sachen noch:
@DragonGate
Wenn ich einen neuen Mitarbeiter hinzufüge, kann der ruhig auf alle Blätter erscheinen. Ab einem bestimmten Monat ist nicht notwendig.

@Scientist
zu Punkt 1: Da kann ich doch die Tabelle vom Blatt "Mitarbeiter" nutzen, oder?
zu Punkt 4: Warum an ein unteres Ende einfügen? Warum kann ich das im Blatt "Januar" nicht ab Zeile 3 einfügen?

Wenn ich das mit dem Makrorekorder mache, wie von @Scientist aufgeführt, würde es funktionieren dies nur für das Blatt "Januar" zu machen und das Blatt dann noch 11x kopieren und in "Februar" bis "Dezember" umzubenennen?
Ist Excel also so schlau das Makro dementsprechend selbstständig zu erweitern, oder ist das wunschdenken?

 

[Scientist]

zu Punkt 1: Da kann ich doch die Tabelle vom Blatt "Mitarbeiter" nutzen, oder?

Ja

zu Punkt 4: Warum an ein unteres Ende einfügen? Warum kann ich das im Blatt "Januar" nicht ab Zeile 3 einfügen?

Weil der Makrorekorder nur das 1 zu 1 ausfuehrt was er aufgenommen hat.
Wenn du es waehrend der Aufnahme in Zeile 3 deine Daten eingefuegt hast, macht es das immer,
egal ob dort nun etwas steht oder nicht.
Deswegen ans Ende, weil dort nie etwas stehen sollte.

Wenn ich das mit dem Makrorekorder mache, wie von @Scientist aufgeführt, würde es funktionieren dies nur für das Blatt "Januar" zu machen und das Blatt dann noch 11x kopieren und in "Februar" bis "Dezember" umzubenennen?
Ist Excel also so schlau das Makro dementsprechend selbstständig zu erweitern, oder ist das wunschdenken?

Dazu muesstest du die Eintraege im Skript anpassen.
Denn dort steht uebersetzt "rufe Blatt 'Januar'" auf.
Muesstest du ausprobieren, aber der Code vom Makrorekorder ist immer ziemlich unuebersichtlich.

 

[Surtia]

Ist Excel also so schlau das Makro dementsprechend selbstständig zu erweitern, oder ist das wunschdenken?

Leider Wunschdenken.
Zellbezüge, Registerbezüge usw. werden automatisch angepasst.
Makros sind davon ausgenommen und müssen manuell korrigiert werden.
Du kannst jedoch Registerblätter im Makro per Nummer ansprechen. Somit ist es egal welchen Namen sie tragen. Allerdings sollte die Reihenfolge der RB in dem Fall nicht geändert werden. Mit dem Arbeitsmappenschutz kannst Du dies bedingt gewährleisten.

 

[kafi112]

So, zeitlich habe ich es nun doch nicht eher geschafft.
Fast hätte ich es hinbekommen.
Ich habe mir ein Makro aufgezeichnet, wobei bei der Ausführung die Fehlermeldung kam, das das Makro zu groß ist (größer als 64bit).
Nun habe ich mir mehrere kleine Makros aufgezeichnet, wobei jedes einzelne für sich auch das macht was es soll.
Nun habe ich versucht alle Makros der Reihe nach durch ein anderes Makro zu starten, was aber nicht funktioniert.
Auf dem 1. Blatt (Mitarbeiter) habe ich eine Schaltfläche (Nach Nachname sortieren), über die die Makros der Reihe nach gestartet werden sollen.
Natürlich soll jedes Makro erst starten, wenn das Vorherige komplett durchgelaufen ist.
Die Makros heißen:
Blattschutz_aufheben
Namen_einfügen
Sortieren
Sortieren_nur_Mitarbeiter
Blatt_schützen
und sollen auch in dieser Reihenfolge ablaufen.

Edit:
Hat sich erledigt.
So hat es nun funktioniert:
Sub Sortieren_nach_Nachname()
'Sortieren_nach_Nachname Makro
Call Blattschutz_aufheben
Call Namen_einfügen
Call Sortieren
Call Sortieren_nur_Mitarbeiter
Call Blatt_schützen
End Sub

Edit 2:
Noch eine andere Frage:
Ich habe in den Blättern Januar bis Dezember die Zellen A3-B203 gesperrt, wenn der Blattschutz aktiv ist.
Wenn das Makro aber durchgelaufen ist und die Namen in die Blätter übertragen hat, bleiben die Zellen entsperrt.
Kann ich das irgendwie ändern?

Edit 3:
Hat sich ebenfalls erledigt.
Habe mir dafür ein zusätzliches Makro aufgezeichnet.

 

[Scientist]

Interessant zu wissen, dass es ein Limit gibt ...