Office: (Office 2010) Excel Makro auf allen Seiten

Helfe beim Thema Excel Makro auf allen Seiten in Microsoft Excel Hilfe um das Problem gemeinsam zu lösen; Hallo, ich habe ein Problem mit den Makros (ist mein erster Versuch) vielleicht kann mir jemand von Euch dabei helfen: Wir haben für jedes Jahr eine... Dieses Thema im Forum "Microsoft Excel Hilfe" wurde erstellt von BlinkyBill, 16. November 2018.

  1. BlinkyBill hat Ahnung

    Excel Makro auf allen Seiten


    Hallo, ich habe ein Problem mit den Makros (ist mein erster Versuch) vielleicht kann mir jemand von Euch dabei helfen:

    Wir haben für jedes Jahr eine Excel Datei. Auf dem Tabellenblatt 'Jahresübersicht' steht ab Zeile A4 die Namen der Mitarbeiter. In Spalte B die Kostenstelle C,D,E,F weiter Infos.
    Ich habe es inzwischen geschafft, dass sich bei 'Jahresübersicht' automatisch die Namen nach Alphabet sortiert.
    Auf dem Tabellenblatt 'Jan.' passt es sich mit dem Drücken von Alt+F8 an.
    Aber auf Feb. März. etc. ändert es sich nicht.
    Auf dem Tabellenblättern für die Monate stehen die Namen der Mitarbeiter ab Zeile A6. Jeder Monatstag hat eine eigene Spalte, dort werden Urlaubs und Krankentage Manuell eingetragen.

    Das ist der Code den ich bisher habe


    Es wäre nett wenn mir jemand helfen könnte und mir sagt, was an dem Code verändert werden muss, dass die Änderung bei dem Einfügen von neuen Namen auch auf den Blättern Feb. März. April etc auch aktualisiert.


    Ich schätze mal es muss irgendwo

    hin. Aber wo?

    Am besten auch noch ohne Alt-F8 drücken zu müssen.
    Was auch noch ein Problem ist, ist dass sich die Manuell eingetragenen Daten nicht mitsortieren. So kann es sein dass die U, oder die K plötzlich nicht bei Karl stehen sondern bei Kevin, wenn ein Neuer Mitarbeiter mit dem Namen Anton dazu kommt. Die Manuell eingetragenen bleiben in der Zeile stehen wo ich sie eingetragen habe.

    Ach ja ich bin absoluter Neuling in dem Bereich, es wäre also nett wenn ihr bei einer Antwort nicht mit Fachbegriffen um euch werft. Wenn mir jemand helfen kann wäre es nett den Kompletten Code hier zu Antworten und die Erklärungen (muss nicht sein) danach.

    Wäre wirklich nett wenn ihr mir helft.
     
    Zuletzt bearbeitet: 16. November 2018
    BlinkyBill, 16. November 2018
    #1
  2. BlinkyBill hat Ahnung
    Hier hab ich die Datei mal mit angehängt.
     
    BlinkyBill, 16. November 2018
    #2
  3. steve1da Office Guru
  4. BlinkyBill hat Ahnung

    Excel Makro auf allen Seiten

    Hast du jetzt nichts besseres zu tun, als in allen Foren nach mir zu suchen?
    Was glaubst du wohl warum ich die Frage in mehreren stelle? Wenn mir in dem einen geholfen worden werde würde ich wohl kaum auch in Anderen fragen,
     
    BlinkyBill, 16. November 2018
    #4
  5. steve1da Office Guru
    Hola,

    du du hast keine Ahnung warum ich das getan habe, oder? Weil DU es nicht getan hast! Du lässt die Leute hier im Forum noch mal über dein Problem nachdenken und dann schlagen Sie evtl. etwas vor, was im anderen Forum schon vorgeschlagen wurde. Super, umsonst gearbeitet. Dir ist das natürlich egal, irgendwann hast du ja deine Lösung.

    Gruß,
    steve1da
     
    steve1da, 17. November 2018
    #5
  6. BlinkyBill hat Ahnung

    Wenn man mir dir etwas vorgeschlagen hätte was ich gebrauchen kann hätte ich es wohl nicht gemacht. Im Grunde hat mir nur ein User geholfen mit einem Code und nicht mit Kommentaren wie "Mach doch alles auf eine Seite".
     
    BlinkyBill, 17. November 2018
    #6
  7. Exl121150 Erfahrener User
    Hallo,

    in der beiliegenden Excel-Datei habe ich deine Makros modifiziert, sodass
    1) wenn du im Arbeitsblatt "Jahresübersicht" einen weiteren Namen hinzufügst am Ende der Liste von Spalte A, so wird dieser an der richtigen Stelle einsortiert zusammen mit den Werten der Spalten B:AL dieser Zeile.
    2) Das Gleiche wird dann auch bei den Monatsblättern automatisch durchgeführt, wobei die Spaltenwerte einer Zeile nicht von den Namen der Spalte A getrennt werden.

    3) Analoges gilt auch, wenn du einen Namen aus der Namensliste der "Jahresübersicht" entfernst: d.h. du leerst die betreffenden Namenszelle. Das hat zur Folge, dass diese Zeile (mit den Spalten A:AL) ans Ende der Namensliste verschoben wird. Das erfolgt ebenfalls in den Monatsblättern genauso.

    4) Entscheidend bei den Punkten 1)+2)+3) ist, dass das Namensanfügen bzw. das Namensentfernen ausschließlich im Arbeitsblatt "Jahresübersicht" erfolgen darf - in den Monatsblättern erfolgt dies dann automatisch.

    5) Beibehalten habe ich das Makro, das über die Tastenkombination Alt+F8 erreichbar ist: "KopiereNamen_nach_Monatsblätter". Dieses wird normalerweise nicht benötigt. Ich kann mir aber vorstellen, dass es zu Jahresbeginn sinnvoll sein kein, um die Namen der "Jahresübersicht" in die Monatsblätter zu übertragen. Dabei werden nur die Namen der Spalte A übertragen, also keine Werte weiterer Spalten.

    6) Die Makros befinden sich im allgem. Codemodul "Modul1" und im Codemodul vom Arbeitsblatt "Jahresübersicht". Ich habe überall reichlich Kommentar hinzugefügt, um die Funktionsweise zu verdeutlichen.
     
    Exl121150, 17. November 2018
    #7
  8. BlinkyBill hat Ahnung

    Excel Makro auf allen Seiten

    WOW!!!!!! Vielen vielen Dank!!!!!!!

    Das ist wirklich Geil.

    VIELEN VIELEN DANK!!!

    sollte ich irgendwann einmal die Weltherrschaft ergriffen haben, mache ich zu meiner Rechten Hand :P

    Vielen Dank
     
    Zuletzt bearbeitet: 17. November 2018
    BlinkyBill, 17. November 2018
    #8
  9. BlinkyBill hat Ahnung
    Noch einmal vielen Dank, ich hab es mir jetzt einmal durchgelesen...nur zwei Frage hätte ich jetzt noch.
    Welcher von den Codes war jetzt dafür verantwortlich, dass die Urlaubstage von Paula auch bei Paula bleiben?
    Und wie hast du es geschafft, dass man nicht immer Alt+F8 drücken muss?

    Beim nächsten mal möchte ich es immerhin alleine schaffen
     
    Zuletzt bearbeitet: 18. November 2018
    BlinkyBill, 17. November 2018
    #9
  10. Exl121150 Erfahrener User
    Hallo,

    Sobald du über die Tastatur in einer Excel-Arbeitsblattzelle eine Datenänderung durchführst - sei es, dass du in einer Zelle Text oder Zahlen oder Datumsangaben etc. eingibst, oder sei es, dass du eine Zelle mit Inhalt entleerst - wird in der Excel-Anwendung ein "Change-Event" ausgelöst. Genau auf dieses Change-Ereignis kannst du per VBA-Code reagieren entweder auf Arbeitsmappenebene oder auf Arbeitsblattebene:
    Code:
    'Code auf Arbeitsblatt-Ebene: zB. im Codemodul für Arbeitsblatt "Jahresübersicht"/Tabelle1:
    [COLOR="#0000CD"]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
      '.....
      'Der Aufrufparameter "Target" hat den Typ "Range", das entspricht im Arbeitsblatt dem Typ "Bereich/Bezug".
      'Er kennzeichnet genau die Zelle (bzw. den Zellbereich), in der die Datenänderung stattfand. 
      'Vorsicht ist insofern geboten, weil dieses Ereignis nur auf Zellinhaltsänderung von außen reagiert, 
      'nicht aber auf Zellinhaltsänderung, die durch Formeln in Zellen verursacht werden.!!
      '.....
    [COLOR="#0000CD"]End Sub[/COLOR]
    
    'Code auf Arbeitsmappen-Ebene - im Codemodul für "DieseArbeitsmappe" ([B]Code ist in deiner Datei nicht enthalten![/B]):
    [COLOR="#0000CD"]Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)[/COLOR]
      '...…
      'Zusätzlich zu Parameter "Target" gibt es hier den Parameter "Sh" vom Typ "Object", wobei damit ein "Arbeitsblatt" gemeint ist.
      'Es ist klar, dass diese "Callback"-Prozedur diesen 2. Parameter benötigt, 
      'damit man in ihr auch weiß, in welchem Arbeitsblatt das Änderungsereignis stattfand.
      '......
    [COLOR="#0000CD"]End Sub[/COLOR]
    
    Gibst du im Arbeitsblatt "Jahresübersicht" in die erste freie Zelle (zB. Zelle A34) von Spalte A den Namen "Erwin" ein, so wird ein Change-Ereignis ausgelöst und deine oben definierte Prozedur "Worksheet_Change" mit dem Parameter "Target"=Zellbezug A34 aufgerufen und ihre Befehls-Innereien ausgeführt.
    1. Zuerst wird getestet, ob Target aus nur 1 Zelle besteht - falls nicht, Prozedurabbruch.
    2. Dann wird überprüft ("If not ...is Nothing then") , ob Target sich im Zellbereich "A4:A10000" befindet - nur dann werden die weiteren Punkte ausgeführt.
    3. Der Inhalt von Zelle Target wird in der Variablen "strName" abgespeichert, also zB. - strName:="Erwin".
    4. Der nächste Befehl ist der Sortierbefehl für das Arbeitsblatt "Jahresübersicht": Hier werden alle Zeilen des Bereiches "A4:AL10000" sortiert, wobei als Sortierkriterium die Spalte genommen wird, in der sich Zelle "A4" (=key1) befindet. Dadurch wird die ganze Zeile 34 (also A34:AL34) mit "Erwin" in A34 und eventuellen weiteren Werten in den Folgezellen dieser Zeile (B34, C34, …, AL34) umsortiert und zB. zwischen "Emma" (A18) und "Eva" (bisher A19 und ab jetzt A20) eingefügt.
    5. Dieses Namensanfügen mit anschließendem Einsortieren muss jetzt auch noch in jedem der 12 Monatsblätter ("For Each VarMon in MonatsListe() … Next VarMon") durchgeführt werden:
      Dazu wird zu jedem Monat (zB. "Jan." …) das Arbeitsblattobjekt "wsZielblatt(..)" ermittelt und die zugehörige benutzte letzte Arbeitsblattzeile-Nummer ("zlEnd").
      Ist die Variable "strName" nicht leer ("If Len(strName$)=0 then …. else "), ist also ein Name anzufügen (".Cells(zlEnd+1,1).Value = strName$").
      Dieser Name und die Folgezellen in Blattzeile "zlEnd" (in Spalte A, Spalte B, …, Spalte AL) sind im Bereich "A6:AL10000" zeilenweise umzusortieren, wobei wieder als Sortierkriterium Spalte A ("key1:=.Range("A6")" dient.
    Entfernst du im Arbeitsblatt "Jahresübersicht" den vorhin eingefügten "Erwin" wieder aus Zelle A19, so wird dadurch diese Zelle zu einer leeren Zelle. Es wird wieder das "Change-Event" ausgelöst, nur eben mit dem Unterschied, dass die Zeile A19:AL19 mit dem Leerwert in A19 in die 1. Leerzeilen-Zeile des Bereiches A4:AL10000 sortiert wird. Dies wird ebenfalls wie oben in den 12 Monatsblättern durchgeführt.

    Mit diesem Mechanismus von oben kannst du nicht nur Namen entfernen und Namen anfügen=einfügen, sondern auch Namensänderungen durchführen, ohne die zum ursprünglichen Namen gehörigen Daten beim neuen Namen zu verlieren:
    1) Zu ändernden Namen aus Spalte A von "Jahresübersicht" löschen=entleeren - dadurch wird diese Zeile in die 1. Zeile nach dem Datenblock sortiert.
    2) In diese so aussortierte Zeile gibst du den neuen Namen in Spalte A ein - so wird diese Zeile mit den zugehörigen Daten an der neuen Zeilenposition wieder eingefügt.
     
    Exl121150, 19. November 2018
    #10
  11. BlinkyBill hat Ahnung
    Auch an dich VIELEN VIELEN VIELEN DANK!!!!

    Meine Kollegen sind davon begeistert. Mit der Osterformel und so muss man jeztt nur den den ersten Tag des Jahres ändern und die Tabelle kann bis zum ende der Menschheit benutzt werden ohne dass man Feiertage raussuchen muss :P Und ohne neue Mitarbeiter einfach dranzuhängen oder mühsam selbst sortieren zu müssen

    Jetzt habe ich aber noch eine kleine Frage

    Wenn ich nicht nur Zeile A übernehmen will, sondern auch Zeile B. Weil dort die Kostenstellen drin stehen. Wie kann ich das machen, dass mehre Daten aus der "Jahresübersicht" genommen werden? Also auch die Kostenstellen oder vielleicht sogar noch eine 3 oder eine 4 Info wie die Urlaubstage. Die nicht in Zelle C stehen sodern am Ende der Tabelle

    Da habe ich auch schon ein wenig rumprobiert nur leider ohne Erfolg :-( :-(
     
    Zuletzt bearbeitet: 19. November 2018
    BlinkyBill, 19. November 2018
    #11
  12. Exl121150 Erfahrener User
    Hallo,

    du willst wohl sagen: Spalte A bzw. Spalte B ?

    Die blau/rot eingefärbten Stellen im Makro sind zu ändern. Dabei bedeuten die 3 roten 2er die Anzahl zu kopierender Spalten. Diese müssen nebeneinander liegen - in diesem Fall also die Spalten A:B.
    Code:
    Dim varMon As Variant, zlEnd As Long
    [COLOR="#0000CD"]Dim ZuKopierendeWerte As Variant[/COLOR]
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       If Target.Cells.Count <> 1 Then Exit Sub 
       
       If Not Application.Intersect(Target, Range("A4:A10000")) Is Nothing Then
           [COLOR="#0000CD"]ZuKopierendeWerte = Target.Resize(1, [COLOR="#FF0000"]2[/COLOR])[/COLOR]
           
           Me.Range("A4:AL10000").Sort key1:=Me.Range("A4"), order1:=xlAscending, _
              Header:=xlNo, ordercustom:=1, MatchCase:=False, _
              Orientation:=xlTopToBottom
              
           For Each varMon In MonatsListe()
              With wsZielblatt(varMon, zlEnd)
                If Len([COLOR="#0000CD"]ZuKopierendeWerte(1, 1)[/COLOR]) = 0 Then
                   [COLOR="#0000CD"].Cells(Target.Row + 2, 1).Resize(1, [COLOR="#FF0000"]2[/COLOR]) = ZuKopierendeWerte[/COLOR]
                Else
                   [COLOR="#0000CD"].Cells(zlEnd + 1, 1).Resize(1, [COLOR="#FF0000"]2[/COLOR]) = ZuKopierendeWerte[/COLOR]
                End If
                .Range("A6:AL10000").Sort key1:=.Range("A6"), order1:=xlAscending, _
                      Header:=xlNo, ordercustom:=1, MatchCase:=False, _
                      Orientation:=xlTopToBottom
              End With
           Next varMon
           
       End If
    
    End Sub
    
    Diese Makroänderung ist in der angefügten Exceldatei enthalten.
     
    Exl121150, 19. November 2018
    #12
  13. BlinkyBill hat Ahnung

    Excel Makro auf allen Seiten

    Habe mich nicht klar ausgedrückt …tut mir leid.

    Ich meine, wenn ich Spalte A und B übernehmen will. Spalte A bei "Jahresübersicht" soll in den Monaten bei A stehen und Spalte B von "Jahresübersicht" auch bei Spalte B in den Monaten.

    Und wenn ich dann sogar noch eine Spalte wollen würde, die noch hinter Spalte C liegt. Also Spalte AH oder so
     
    Zuletzt bearbeitet: 19. November 2018
    BlinkyBill, 19. November 2018
    #13
  14. Exl121150 Erfahrener User
    Hallo,

    offenbar bezieht sich die Angabe "Spalte AH" auf die Monatsarbeitsblätter (mit der Beschriftung "Gesamt" in Zelle AH5).
    In welcher Spalte des Arbeitsblattes "Jahresübersicht" sollen die Werte vorgenannter Spalte enthalten sein? Ich muss ja eine Zuordnung festlegen zwischen diesen Spalten, wenn ich deine Wünsche umsetzen soll.

    Bisher hatte ich die beiden Spalten A+B verwendet sowohl im Arbeitsblatt "Jahresübersicht" als auch in den Monatsarbeitsblättern, wobei Spalte A gleichzeitig als Sortierkriterium in beiden Blattarten fungierte.
     
    Exl121150, 20. November 2018
    #14
  15. BlinkyBill hat Ahnung
    Okay auf dem Blatt "Jahresübersicht" stehen die Kostenstellen an Spalte B4 (A4 ist der Name)
    In den Montasblättern beginnt es ab Zeile 7 also müsste die Kostenstelle dort in Spalte B7 stehen
    Der Wert aus Spalte F4 ("Jahresübersicht") soll in den Montasblättern ab Spalte AI 7 stehen.

    Schon mal danke
     
    BlinkyBill, 20. November 2018
    #15
Thema:

Excel Makro auf allen Seiten

Die Seite wird geladen...
  1. Excel Makro auf allen Seiten - Similar Threads - Excel Makro

  2. Speichern mit dem Titel der Zelle A2

    in Microsoft Excel Hilfe
    Speichern mit dem Titel der Zelle A2: Moin moin, Ich habe per Makro einen Arbeitsablauf aufgezeichnet der soweit auch funktioniert. Dieser Endet jedoch im "Speichern Unter" Fenster, welches durch das Klicken von "Drucken als PDF"...
  3. Kontrollkästchen (mehrere) Makro kopieren, Problem: Zellen nicht gegenseitig überschreiben

    in Microsoft Excel Hilfe
    Kontrollkästchen (mehrere) Makro kopieren, Problem: Zellen nicht gegenseitig überschreiben: Hallo zusammen, ich arbeite gerade an einer Vereinfachung für Bestellungen. Ich habe es hingekriegt, dass Kontrollkästchen an zu wählen sind und dann bestimmte Informationen kopiert werden. Jetzt...
  4. Makro Befehl läuft auf Mac nicht

    in Microsoft Excel Hilfe
    Makro Befehl läuft auf Mac nicht: Sheets("Ausschreibungsdeckblatt - " & CStr(id)).Select ActiveWorkbook.SaveAs FileName:=sPath & Application.PathSeparator & sSupplierPrefix & CStr(id) & " " & sFirma & " WS " &...
  5. Komplexes Makro ohne Ahnung :-/

    in Microsoft Excel Hilfe
    Komplexes Makro ohne Ahnung :-/: Hallo Ihr Lieben, ich brauche ganz dringend Hilfe. Ich bin zwar mit Formeln in Excel ganz gut aufgestellt, aber mit Makros leider nicht. Ich muss für meine Eltern und mich viele Versicherungen und...
  6. Excel Datei mit Passwort versehen obwohl Makro

    in Microsoft Excel Hilfe
    Excel Datei mit Passwort versehen obwohl Makro: Hallo, ich muss meine Excel Datei mit einem Passwort versehen oder den Zugriff für Personen beschränken aber ich bekomme eine Meldung dass ich erst alle Makros entfernen muss. Gibt es dafür eine...
  7. VBA Excel in mehrere teilen nach Kostenstelle

    in Microsoft Excel Hilfe
    VBA Excel in mehrere teilen nach Kostenstelle: Hallo liebe Office Community, ich bin mit VBA nicht so bewandert und habe folgenden "Auftrag"... Eine Excel Datei aus SAP soll aufgeteilt werden nach Kostenstelle. Sprich für jede Kostenstelle...
  8. Audiodateien anhand einer Excel sortieren

    in Microsoft Excel Hilfe
    Audiodateien anhand einer Excel sortieren: Hallo zusammen, Ich stehe momentan vor folgendem Problem. Ich muss aus einer Audiobibliothek mit über 5000 Titeln jeweils 200 und 500 heraussuchen und am besten in einen separaten Ordner packen....
  1. Diese Seite verwendet Cookies, um Inhalte zu personalisieren, diese deiner Erfahrung anzupassen und dich nach der Registrierung angemeldet zu halten.
    Auf dieser Website werden Cookies für die Zugriffsanalyse und Anzeigenmessung verwendet.
    Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden