Runde 2: Daten in Excel aufteilen nach Monaten

  • Moin-Moin.

    Ich habe auf dem Tabellenblatt "Kalender" in B1:G367 Daten stehen die ich auf die Monatsblätter (Januar, Februar, ...) aufteilen möchte.
    Erstes Problem, wie verteile ich es automatisch in die jeweiligen Monatstabellenblätter und muss ich die Tabellenblätter händisch erstellen oder geht soetwas auch via VBA?
    Zweites Problem ist der Februar, da dieser ja nicht immer die gleiche Tageszahl hat.

    In B1:B367 (kein Schaltjahr) stehen das Datum vom 01.01.2018 - 31.12.2018, wobei das Jahr dynamisch ist.

    Ich habe mir ein dickes VBA gekauft von Bernd Held, aber da brauche ich einige Tage (ppar Wochen, um erst einmal über das große den Überblick zu bekommen.

    Hat von Euch jemand eine Lösung oder hatte schon einmal ein solches Problem und kann mir helfen?

    Lieben Dank und Moin-Moin.

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

    • Offizieller Beitrag

    Du musst nix händisch erstellen. Was du dynamisch kontrollieren willst, packst du in eine Routine, die beim Öffnen des Workbooks ausgeführt wird (Workbook-Open Event).

    Dann prüfst du, was ist das letzte vorhandene Tabellenblatt (Monat), wieviel sollen sichtbar sein (z.B. -3/aktuell/+3) und entfernts/fügst Tabellenblätter hinzu, deren Inhalt du nach vorgefertigten Abläufen befüllst.

    Du musst für dich entscheiden, was du eventuell beim Entfernen von Tabellenblättern an Daten in ein Log schreibst, als Rückschaumöglichkeit. Denn ich nehme mal an, das soll nicht für x Jahre alle Daten beinhalten.

  • Hallo Alina, das ist kein Problem und in Vba leicht zu erstellen. Je nach Wissenstand, versuche es in einzelne Schritte aufzuteilen und diese mit dem Makorrecorder in Excel aufzuzeichen. Anschließend kannst Du den Code händisch zusammenbauen. Oder stelle hier ein Beispielmappe rein.

    Stefan

  • Moin BugFix & Schleifchen.

    Also ich belese mich gerade erst mit VBA und wie einige ja wissen, ich meine Zeit 24 Tag schon viel zu kurz. Aber ich glaube das könnte ich irgendwie verstehen.

    Ich hänge eine Datei ran. Es die die Datei, so wie ich sie selber zugesendet bekomme habe. Ich habe dann schon mal die Sonntage, Feiertage farblich bedingt formatiert und die Feiertage, die nicht auf einen Sonntag fallen. Dieses ist später nämlich wichtig zu den Berechnungen, die dann in die Folgespalten H bis U entstehen werden.

    Die Daten sollen nun in Monate ausgeteilt werden und von der Logik kann ich BugFix etwas folgen. Aber ich möchte später nur im Kalender tagesaktuelle Daten eingeben, um auf den Monatsblättern dann einen besseren Übersicht zu bekommen und nur monatsspezielle Berechnungen nur noch in die Tabellenblätter einfügen. Aber hier werde ich dann auch Vorlagen machen und die dann in die Tabellen kopieren, wenn es mit den Monaten funktioniert.
    Sollte dann ja das gleiche Prizip sein, nur das die Tabellenblätter Januar bis Dezember dann ja bereits erstellt ist. Das wollte ich auch mit VBA mal sehen ob das geht. Wenn ich das richtig bis jetzt gelesen habe, ist es möglich zu sagen, das wenn in Spalte B3:B368 der Monats ein Januar ist, dann kopiere es, in meinem Fall in das Tabellenblatt "Januar". So würde es auch beachtet werden, wenn es sich um ein Schaltjahr handelt. Habe gerade noch in B368 das Feld mit einer Formel hinterlegt, damit es beim Schaltjahr den 31. Dezember auch anzeigt und nicht den 01. des Folgejahres.

    Alles unwichtige habe ich mal aus dem Kalender genommen. Wird dann meine Beispiel-Datei für den Ordner: "Grundlagen".

    Wenn der dynamische Kalender fertig ist, nimmt der mir sehr sehr viel Berechnungen ab, die immer und immer wieder die gleichen sind. Und genau dafür

    ist Excel ja da. Noch immer steht bei uns auf den Schreibtischen Taschenrechner, außer auf meinem, da läuft der fast immer auf einen der Monitore. Aber ich brauche Zeitersparnis und dieser Kalender, so er denn fertig ist spart mir wöchentlich dann ca. 2-3 Stunden und am Nonatsanfang gut 6 Stunden. Zeit, die ich mir dann nämlich an Überstunden sparen kann. Das Konto ist eh schon wieder überfüllt (Maximum + 72% Stand gestern).

    So, es ist schon wieder mitten in der Nacht. Ich versuche noch mal eineinhalb Stunden zu schlafen. Euch einen schönen Donnerstag und hoffentlich bald mal einen richtigen Winter.

    Download / Anhang: DynKan.rar

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Erstes Problem, wie verteile ich es automatisch in die jeweiligen Monatstabellenblätter und muss ich die Tabellenblätter händisch erstellen oder geht soetwas auch via VBA?

    Beim ersten Start soll das ganze Jahr abgearbeitet werden? Wenn das erfolgt ist werden täglich nur noch die Änderungen in die bereits vorhandenen Tabellenblätter ergänzt? Ist das Richtig so?

    Wie soll es mehrjährig gehändelt werden, stehen Januar 2019 und Januar 2020 auf einem Tabellenblatt?

    Stefan

    Einmal editiert, zuletzt von Schleifchen (15. Februar 2018 um 10:58)

  • Hallochen, in der Arbeitsmappe mit Alt+F11 den Editor öffen

    • ein neues Modul einfügen
    • obigen Code einfügen
    • die Arbeitsmappe als *.xlsm speichern
    • den Cursor in Code setzen und mit F8 Schritt für Schritt ausführen.

    Dann kannst du schön nachvollziehen wie der Code abgearbeitet wird.

    Wie soll der nächste Schritt sein?

  • Moin Schleifchen.

    Erst einmal: MEIN DANK !!!

    Frage:

    Beim ersten Start soll das ganze Jahr abgearbeitet werden? Wenn das erfolgt ist werden täglich nur noch die Änderungen in die bereits vorhandenen Tabellenblätter ergänzt? Ist das Richtig so?Antwort: dieses ist richtig so.

    Frage:

    Wie soll es mehrjährig gehändelt werden, stehen Januar 2019 und Januar 2020 auf einem Tabellenblatt?

    Antwort: Ich habe die Lösung in den VBA Code so geändert und hoffe das es so richtig ist und Du nichts da gegen hast:

    Case 1

    strMonatName = "Januar" & " " & intJahr

    varKopierBereich = wksKalender.Range("B3:G" & 33)

    Deinen Dateianhang konnte ich leider nicht öffnen, aber ich habe es mit C&P eingefügt bekommen.

    Ich habe bei "'kopieren der monatlichen Daten nach Monatsblatt" die Position, wo die Daten hinkopiert werden sollen geändert. Dies ergibt sich aus dem 2. Schritt der wie folgt aussieht:
    Ich habe das Tabellenblatt Monatsvorlage mal erstellt. Auf dem sind drei farbige Felder. In den kommen später Formel. Diese Felder sollen auf jedes erstellte Monatsblatt kopiert werden, das zu dem intjahr gehört. Das ist wichtig, wenn später mehrer Jahre in der Exceltdatei sind, da zum Jahresbeginn

    Änderungen dann nur in der Vorlage zu machen sind.

    ewiger_kalender_0_0_2.rar

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Na dann ist ja alles fast perfekt!

    Diese Felder sollen auf jedes erstellte Monatsblatt kopiert werden, das zu dem intjahr gehört.

    da stehe ich bisschen auf dem Schlauf :) Wenn ich Dir noch helfen soll, dann mach bitte eine fertige Beispieldatei zurecht. Ansonsten lassen sich die Formeln auch per Vba definieren:

    Code
    Range("A3").FormulaLocal = "=SUMME(A1:A2)"
    
    'einfaches Beispiel - alle Terms sind per Variable frei belegbar...
    Dim bytWert as Byte
    bytWert = 6
    
    Range("A3").FormulaLocal = "=SUMME(A1:A" & bytWert  ")"
  • Hallo.

    1.)
    Habe gerade festgestellt, das das VBA an Schaltjahren nicht richtig arbeitet.

    2.)
    Ich wollte folgende Zeile wie folgt ändern:
    Alt: Worksheets(strMonatName).Range("B1:G" & intEinfuegeEnde) = varKopierBereich
    Neu: Worksheets(strMonatName).Range("B7:G" & intEinfuegeEnde) = varKopierBereich

    Aber das geht nicht, da er dann nicht alle Tage es Monats kopier. Wo denke ich da falsch?

    Das zweite Problem machen wir später. Okay? ;)


    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Habe gerade festgestellt, das das VBA an Schaltjahren nicht richtig arbeitet.

    Ja, da habe ich nicht richtig nachgedacht. Wenn der Februar einen Tag länger ist müssen in den Folgemonaten der Anfang UND Ende um jeweils einen Tag verlängert werden.

    Ich werde es mal in ein Array pressen, nur leider nicht heut :)

    Code
    Falsch: Worksheets(strMonatName).Range("B7:G" & intEinfuegeEnde) = varKopierBereich

    Mit obigem Code wird dem Zellbereich die Variable zugewiesen. Beide Bereich müssen die gleich Größe haben. Ergo sollte das dann funktionieren:

    Code
    Richtig: Worksheets(strMonatName).Range("B7:G" & intEinfuegeEnde + 6) = varKopierBereich

    Es gibt noch ne andere Funktion welche den Zielbereich automatisch anpasst, komm aber grad nicht drauf...

    Gruß

    Stefan

  • Moin Stefan.

    Aktuelle Datei in der Anlage.
    Außer dem Schaltjahrproblem bin ich gut vor.
    Das mit dem kopieren von anderen Bereichen bekomme ich auch schon hin. Habe eine Testdatei und probiere viel aus.

    Beim "Formeln auch per Vba definieren", sagt mein Kopf: "Ja, sieht logisch aus", aber die Umsetzung, da komme ich bestimmt auf Dich zurück.

    Bei der beigefügten Datei habe ich drei Buttons. Eines das die jeweiligen Kalenderblätter (Janauar - Dezember) erstellt und dann einen um die Kalenderblätter aus 2018 zu löschen und einen um die Kalenderblätter aus 2020 (Schaltjahr zu löschen. Das habe ich nur gemacht, weil ich dazu händisch keine Lust habe. Die sind später dann nicht mehr da.

    So, bin mal gespannt wie das Schaltjahrproblem zu löschen ist. Ich habe mir das VBA von Dir ausgedruckt und werde jetzt mit einem Cappuccino auf dem Sofa mir das mal, unter zuhilfenahme des Buches "VBA mit Excel" von Bernd Held, in den Kopf "drücken".

    Und Montag wird Beleg ausgefüllt, denn die € 49,90 sind schließlich "beruflich notwendige Bildungskosten" und die trägt mal brav mein Arbeitgeber, womit er noch nie Probleme hatte.

    Stand: 2018/02/17 ewiger_kalender_0_0_3.rar

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Hallo Alina,

    mir ist klar der folgende Vorschlag ist (möglicherweise) extrem trivial. Schreib die Schaltjahre in ein Ini File und frag vor Beginn ab, ob Schaltjahr oder nicht. Oder bring die Schaltjahre in dem Blatt für die Feiertage unter. Einfach eintippen Soviele Zahlen sind das nicht - für die nächsten 80 Jahre :) - wenn Exel die nicht richtig berechnen kann. Den Februar grundsätzlich um einen Tag länger anlegen, und eben nur in Schaltjahren ein Datum einbauen frei nach dem Motto If Schaltjahr = true then.... .

    Durch den Grundsätzlich längeren Februar musst Du das Design nicht durcheinander werfen.

    Vielleicht hilft Dir der Ansatz

    LG

    Peter

    Hinweise auf Suchmaschinen finde ich überflüssig - wer fragt hat es nicht gefunden oder nicht verstanden. Die Antwort gibt sich oftmals schneller als der Hinweis auf Dr. Goggle & Co.

    Ab 19-10-22 ergänzt um:

    Die Welt wird nicht bedroht von den Menschen, die böse sind, sondern von denen, die das Böse zulassen. (Albert Einstein)

  • Hallo Alina, so funzt der Code:-):

  • Moin Stefan.

    Erst einmal herzlichen Dank.
    Nachdem ich die Zeilen 19 bis 67 angepasst habe, da sonst bei den Monaten die letzten Tage fehlen, funktioniert es.
    Aber auch wenn es funktioniert, werde ich mich mal ran machen und versuchen alles zu verstehen.


    Moin Peter.

    Die Lösung von Stefan ist das was ich gesucht habe. Dein Vorschlag, wenn ich es richtig verstanden habe ist eine VBA freie Lösung, da man alles über formeln macht. Sehe ich das richtig?


    Euch beiden und alles anderen na klar auch einen schönen Abend und ein schönes Wochenende.

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Eigentlich nicht. War sozusagen nur eine Nischenlösung für Dein Schaltjahrproblem. Aber vielleicht habe ich ja auch Dein Problem mit den Schaltjahren falsch verstanden.


    LG

    Peter

    Br.......den ganzen Tag Regen...

    Hinweise auf Suchmaschinen finde ich überflüssig - wer fragt hat es nicht gefunden oder nicht verstanden. Die Antwort gibt sich oftmals schneller als der Hinweis auf Dr. Goggle & Co.

    Ab 19-10-22 ergänzt um:

    Die Welt wird nicht bedroht von den Menschen, die böse sind, sondern von denen, die das Böse zulassen. (Albert Einstein)

  • Br.......den ganzen Tag Regen...

    Hier ist das wunderschönste Winterwetter, nur der Schnee fehlt. Eisig kalt, windig und wunderbare Sonnenaufgänge und Sonnenuntergänge mit hellrot leuchtenden Himmel.

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Nachdem ich die Zeilen 19 bis 67 angepasst habe, da sonst bei den Monaten die letzten Tage fehlen, funktioniert es.

    Hallo Alina, tut mir leid...hatte es nicht nochmals geprüft. Aber die Fehler lag nicht in obigen Zeilen, die sind korrekt. Die Ursache war in Zeile 116

    Code
    intEinfuegeEnde = arintZellen(1, bytMonat - 1) - arintZellen(0, bytMonat - 1) + 7

    ist richtig. Vorher war der Einfügebereich 1 Zeile zu klein. Die letzte Zahl war vorher eine 6.

  • Stefan, das ist jetzt okay. Habe ich so geändert !!!

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Moin Moin zu Runde 2 ;)

    Vorab: Meine Beispielexceldatei befindet sich in der Anlage: ewiger_kalender_0_0_7.rar

    Es gibt drei Tabellenblätter zu Beginn: "Kalender", "Feiertage" und "Monatsvorlage".

    Auf dem Tabellenblatt "Kalender" funktioniert alles einwandfrei, Dank Stefan (Username: Schleifchen).
    : Es werden die Monatsblätter erstellt und die erwünschten Daten hinein kopiert. Die beiden Lösch-Button dienen derzeitig nur zum Test.

    Auf dem Tabellenblatt "Feiertage ist ebenfalls alles in Ordnung.

    Auf dem Tabellenblatt "Monatsvorlage" habe ich gelbe und blaue Bereiche, die auch schon richtig in die dementsprechenden Monatsblätter kopier werden.

    Nun zu den aktuellen Problemen:

    1.) Wie kann ich in den Zellen B6:B der Monatsblätter das Format so ändern, das der Wochentag mit angezeigt wird?
    Evtl. sogar eine Lösung, die "Grundsätzlich" diese Einstellung aus dem Quellbereich übernehmen?
    2.) Wie blende ich in den Monatsblättern die Spalten D-F aus?
    3.) Wenn der Kopiervorgang abgeschlossen ist, sieht man auf dem Tabellenblatt "Montasvorlage noch, was zum kopieren markiert wurde und auch dort wo es
    eingefügt wurde, sind die eingefügten ausgewählt. Wie bekomme ich das weg?
    4.) a) Wie kann ich die Spaltenbreite per VBA festlegen?
    b) Wie kann ich die Zeilenhöhe per VBA festlegen?
    5.) Wie ändere ich die Reihenfolge der Tabellenblätter? "Feiertage" und "Monatsvorlage" sollen ganz nach hinten (re.) , Kalender immer ganz vorne (li.)

    Modul 3 habe ich dann doch schon alleine hin bekommen. Ist vielleicht nichts großes, aber ich bin ja mal gerade angefangen mich damit zu Beschäftigen.
    Aus diesem Grund dokumentiere ich gerne auch jeden Schritt.


    Ich bin dann ja mal gespannt und nachdenklich. :/


    Zu Beitrag Nr. 8 noch kurz:

    ....

    da stehe ich bisschen auf dem Schlauf

    Und mit Modul-3 ist das Problem gelöst, was ich wohl nicht richtig erklären konnte und Du somit auf dem Schlauch gestanden bist.

    Ich habe das erst nur für 2018 erstellt und dann geschaut wie Du die dynamik für das Kalenderjahr geschrieben hast. Das habe ich dann auf Modul 3 umgesetzt und siehe da, es funktioniert. Sicherlich nicht die beste Lösung, aber es ist eine Anfängerinnenlösung.


    Dann mal einen schönen Tag noch.

    Lieben Gruß,
    Alina

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    Geheime Information: ;)
    OuBVU5ebLhHu5QvlnAyQB4A7SzBrvWulwL7RLl2BdH5tI6sIYspeMKeXMSXl

  • Hallo Alina, die Höhe, Breite und Sichtbarkeit kannst Du über die Eigenschaften einstellen:

    So kannst Du es in die Schleife mit einbauen und alles läuft von selbst.

    5.) Wie ändere ich die Reihenfolge der Tabellenblätter? "Feiertage" und "Monatsvorlage" sollen ganz nach hinten (re.) , Kalender immer ganz vorne (li.)

    Code
    Sheets.Add After:=Sheets(bytMonat + 1)

    Hier wird festgelegt wo das Tabellenblatt eingefügt wird. Lösche +1, dann passt es wieder.

    3.) Wenn der Kopiervorgang abgeschlossen ist, sieht man auf dem Tabellenblatt "Montasvorlage noch, was zum kopieren markiert wurde und auch dort wo es
    eingefügt wurde, sind die eingefügten ausgewählt. Wie bekomme ich das weg?

    C&P ist die langsamste Variante.

    Mit der Schleife entfällt das lästige selectieren :)

    Frage: Welche mathematische Grundlage liegt dem Bereich "Blau" zugrunde?

    Bis später

    Stefan