Office: (Office 2007) Datenbereiche synchron erweitern (Zeilen einfüge/löschem/Inhalt kopieren)

Hallo zusammen,

ich habe mich einmal an einem Zeitplan versucht, was soweit auch bisher ganz gut geklappt hat.

Die Mitarbeiterliste habe ich in den Monaten bzw. auch in anderen Arbeitsblättern mit = verknüpft.

Das ist schlecht, falls ein Mitarbeiter hinzukommt, zerschießt es mir meine kompletten Aufstellungen (Zeile einfügen bei Mitarbeiter)

Was ich also benötige (ich denke das ist nur mit Makro möglich) ist:
1. die Mitarbeiterliste soll dynamisch änderbar sein (Zeilen einfügen/löschen für hinzufügen neuer Mitarbeiter)
2. Bei diesen Aktionen sollen sich die Zeilen auf Tabellenblatt: Januar, Monatsübersicht etc. entsprechend mitverschieben

Wichtig ist also dass die Änderungen synchron auf den anderen Tabellenblättern passieren (keine Gruppierung von Arbeitsblättern). Das wäre manueller Aufwand und zu Fehleranfällig

Die Tabelle Mitarbeiterliste ist die führende Liste, in allen anderen Sheets werden lediglich die Zeilen eingetragen.

Eine Beachtung der Sortierung ist nicht notwendig, die Mitarbeiterliste wird nicht sortiert und ist in sofern statisch bis eben einer geht oder kommt.

Falls ihr einen anderen Ansatz habt, was man hier "anders" machen könnte, immer her damit, bin für alle Vorschläge offen.

Ich hoffe ihr versteht meine Problematik, in Excel Formeln bin ich relativ fit, nicht aber in VBA etc. und das wird wohl hier zum Einsatz kommen müssen.

Schön wäre es, wenn das automatisch funktioniert, alternativ auch über nen Button oder ausführen des Makros, das ist dann egal.

Bin schon seit 2 Tagen am suchen aber habe noch nichts entsprechendes gefunden, evtl. habe ich auch nur nach dem falschen Schlagwörtern gesucht..

Ich danke euch schon einmal für eure Mühe, falls etwas unklar ist werde ich dies schnellstmölich beantworten.

P.S: Ich bin jetzt nicht der Meinung, dass hier ein komplett fertig Makro rausspringt, analog irgend etwas basteln schaffe ich meistens :)

Danke für eure Tipps

 

Hi,

benutze das Change-Ereignis des Tabellenblattes "Mitarbeiterliste" um neue Mitarbeiter einzutragen. Laufe dazu in einer Schleife über alle Tabellenblätter - da sie anscheinend alle identisch aufgebaut sind, kannst du Target.Address verwenden um die Zelle in Spalte A zu finden, in die der Eintrag erfolgen soll.
Benutze das Doppelklick-Ereignis des Tabellenblattes "Mitarbeiterliste" um Mitarbeiter zu löschen - Doppelklick-Ereignis bietet eine gewisse Sicherheit, dass nicht versehentlich etwas gelöscht wird. Vorgehensweise ist dann dieselbe wie für das Change-Ereignis.

Bis später,
Karin

 

Hallo,
erst einmal Danke für ie Unterstützung!
ich habe mal folgendes zum testen gemacht:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 2 And Target.Row <= 100 And Target.Column >= 2 And Target.Column = 1
Then MsgBox ("hallo")
Exit Sub
End Sub


D.h. ich grenze in dem Blatt auf Spalte A ein und von Zeile 2-100

Kann ich das Doppelklick Ereignis in den selben Code einbauen?

Desweiteren habe ich noch folgendes Makro "gebaut":

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> "$A$3" Then Exit Sub
Application.EnableEvents = False
Worksheets("Tabelle1").Range("A4").Value = Target.Value
Worksheets("Tabelle3").Range("A4").Value = Target.Value
Application.EnableEvents = True
End Sub

Ist Target as Excel Range oder Range korrekt?

Danke nochmal für eure Hilfe und Geduld, wie gesagt, VBA ist nicht meine Welt =)

 

Hi,

ich würde es mit diesem Code versuchen:

Code:

Option Explicit
Dim blnLoeschen As Boolean

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' für Löschen von Einträgen
    Dim wksTab As Worksheet
    ' nur in Spalte A ab Zeile 2 reagieren
    If Target.Column = 1 And Target.Row > 1 Then
        ' doppelgeklickte Zelle is nicht leer
        If Target <> "" Then
            ' Variable auf True setzen damit das Changeereignis nicht ausgelöst wird
            blnLoeschen = True
            ' Schleife über alle Blätter
            For Each wksTab In Worksheets
                ' laufendes Blatt ist nicht aktives Blatt
                If wksTab.Name <> ActiveSheet.Name Then
                    ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
                    ' relevante Zeile löschen
                    If wksTab.Name = "Monatsübersicht" Then
                        wksTab.Rows(Target.Row + 1).Delete
                    Else
                        wksTab.Rows(Target.Row + 2).Delete
                    End If
                End If
            Next wksTab
            ' Zeile löschen in der doppelgeklickt wirde
            Target.EntireRow.Delete
            Cancel = True
            blnLoeschen = False
        End If
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' für Neueinträge
    Dim wksTab As Worksheet
    ' nur in Spalte A ab Zeile 2 reagieren
    If Target.Column = 1 And Target.Row > 1 Then
        ' nur ausführen wenn Zeile NICHT durch das Doppelklickereignis gelöscht wurde
        If blnLoeschen = False Then
            ' es wurde kein Zellinhalt gelöscht, dann Neueintrag in die anderen Blätter
            If Target <> "" Then
            ' Schleife über alle Blätter
                For Each wksTab In Worksheets
                ' laufendes Blatt ist nicht aktives Blatt
                    If wksTab.Name <> ActiveSheet.Name Then
                        ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
                        ' relevante Zeile löschen
                        If wksTab.Name = "Monatsübersicht" Then
                            wksTab.Range(Target.Address).Offset(1, 0) = Target
                        Else
                            wksTab.Range(Target.Address).Offset(2, 0) = Target
                        End If
                    End If
                Next wksTab
            End If
        End If
    End If
End Sub

Bis später,
Karin

 

Hi Karin, ich habe deine Version getestet und das löschen klappt super, ebenso das umbearbeiten von existierenden Mitarbeitern (umbenennen)
Wirklich Super,

eine Sache fehlt aber noch (analog zum Zeilen löschen)

Wen ich z.B. zwischen Zeile A3 und A4 eine neue Zeile einfüge, dann fügt er diese nicht auch in die anderen Blätter ein.

Ich habe mir überlegt das in die Change funktion mit einzubauen, also in etwa:

Code:

            If Target <> "" Then
            ' Schleife über alle Blätter
                For Each wksTab In Worksheets
                ' laufendes Blatt ist nicht aktives Blatt
                    If wksTab.Name <> ActiveSheet.Name Then
                        ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
                        ' relevante Zeile löschen
                        If wksTab.Name = "Monatsübersicht" Then
                      '  wksTab.Rows(Target.Row + 1).Insert
                        wksTab.Range(Target.Address).Offset(1, 0) = Target
                        Else
                      '  wksTab.Rows(Target.Row + 2).Insert
                        wksTab.Range(Target.Address).Offset(2, 0) = Target
                        End If
                    End If
                Next wksTab
            ElseIf Target = "" Then
            For Each wksTab In Worksheets
                ' laufendes Blatt ist nicht aktives Blatt
                    If wksTab.Name <> ActiveSheet.Name Then
                        ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
                        ' relevante Zeile löschen
                        If wksTab.Name = "Monatsübersicht" Then
                        wksTab.Rows(Target.Row + 1).Insert
                      '  wksTab.Range(Target.Address).Offset(1, 0) = Target
                        Else
                        wksTab.Rows(Target.Row + 2).Insert
                      '  wksTab.Range(Target.Address).Offset(2, 0) = Target
                        End If
                    End If
              Next wksTab
           

Das haut aber absolut gar nicht hin :D

Wenn ich in den originl Code einfach nur noch:

Code:

                        wksTab.Rows(Target.Row + 1).Insert
                        wksTab.Rows(Target.Row + 2).Insert

in die Bedingung einfüge, dann macht er mir logischerweise bei jeder Änderungen / Bearbeitung auch noch eine Zeile rein.

Hast du noch eine Idee?

Nochmal vielen Dank für deine Hilfe

 

Hi,

für das Einfügen von Zellen kannst du das Change-Ereignis nicht verwenden, denn die Prüfung ob Target = "" ist, würde ja auch zutreffen, wenn man von Hand einen Zellinhalt löscht. Ich würde stattdessen einen extra Schalter (Formular-Steuerelement) verwenden, der dann die markierten Zeilen einfügt. Folgenden Code in ein allgemeine Modul (und Makro dem Schalter zuerweisen):

Code:

Sub Einfuegen()
    Dim wksTab As Worksheet
    Dim lngStart As Long
    Dim lngAnzahl As Long
    ' Variable auf True, damit das Change-Ereignis nicht ausgelöst wird
    blnLoeschen = True
    ' Anzahl der einzufügenden Zeilen
    lngAnzahl = Selection.Rows.Count - 1
    For Each wksTab In Worksheets
        ' Startzeile die markiert wurde
        lngStart = Selection.Cells(1).Row
        ' laufendes Blatt ist nicht aktives Blatt
        If wksTab.Name <> ActiveSheet.Name Then
            ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
            ' Startzeile festlegen
            If wksTab.Name = "Monatsübersicht" Then
                lngStart = lngStart + 1
            Else
                lngStart = lngStart + 2
            End If
            ' Zeilen einfügen
            wksTab.Rows(lngStart & ":" & lngStart + lngAnzahl).Insert
        End If
    Next wksTab
    ' im aktiven Tabellenblatt die markierten Zeilen einfügen
    Selection.Rows.Insert
    blnLoeschen = False
End Sub

Lösche außerdem die Zeile

Code:

Dim blnLoeschen As Boolean

aus dem Codemodul des Tabellenblattes und schreibe stattdessen diese Zeile in ein allgemines Modul:

Code:

Public blnLoeschen As Boolean

Auf diese Weise kann man die Variable aus mehreren Codemodulen verwenden.


Im Change-Ereignis musst du außerdem berücksichtigen, dass eventuell mehr als 1 Zeile eingefügt werden soll, der bisherige Code würde dann auf einen Fehler laufen - habe ich angepasst durch die If-Abfrage ob mehr als 1 Zelle markiert ist.

Bis später,
Karin

 

Hi,

ich hab das jetzt mal ausgiebig getestet, das klappt super, vielen vielen DANK!

Vielen Dank auch für deine Kommentare im Code, damit komm ich dann auch in nem halben Jahr noch zurecht =)

"Legende und Jahresübersicht" habe ich rausgenommen:

Code:

                ' Schleife über alle Blätter
                    For Each wksTab In Worksheets
                    ' laufendes Blatt ist nicht aktives Blatt
                        If wksTab.Name = "Legende" Then
                        ' bei Legende passiert nichts
                        ElseIf wksTab.Name = "Jahresübersicht" Then
                        ' bei Jahresübersicht passiert nichts
                        ElseIf wksTab.Name <> ActiveSheet.Name Then
                            ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
                            ' relevante Zeile löschen
                            If wksTab.Name = "Monatsübersicht" Then
                                wksTab.Range(Target.Address).Offset(1, 0) = Target
                            Else
                                wksTab.Range(Target.Address).Offset(2, 0) = Target
                            End If
                        End If
                    Next wksTab

Ist wahrscheinlich nicht so elegant, aber das passt für mich so.

Das sind die einzigen 2 Mappen, auf denen die Mitarbeiterliste nicht drin ist, da hatte ich den Ehrgeiz das selber zu machen.
Hab trotzdem bestimmt ne halbe Stunde rumprobiert, weil ich die Mappen zuerst an der falschen Stelle abfangen wollte :D

Bin dann aber nochmal durch die Kommentare von dir durch, das macht dann schon Sinn.

Hätte das ohne deine Hilfe nicht hinbekommen, ich weiss warum ich "Integrator" wurde ;)

Habe mir auch deine Page angeschaut, da gibts auch ein paar coole Sachen welche ich evtl. noch einbaue.

Vielen Dank für deine große Hilfsbereitschaft, auch wenn das mein erster Post/Thread hier war!

 

Hi,

freut mich, dass dir die Kommentare weitergeholfen haben. Deine Lösung ist völlig Ok, allerdings kann man auch anderes herangehen, wenn man mehrere Tabellen ausschließen will - z.B. so:

Code:

    For Each wksTab In Worksheets
        ' Startzeile die markiert wurde
        lngStart = Selection.Cells(1).Row
        ' laufendes Blatt ist nicht aktives Blatt und nicht Legende und nicht Jahresübersicht
        [COLOR=#0000ff]If wksTab.Name <> ActiveSheet.Name And wksTab.Name <> "Legende" And wksTab.Name <> "Jahresübersicht" Then[/COLOR]
            ' Monatsübersicht hat einen anderen Aufbau als die anderen Blätter
            ' Startzeile festlegen
            If wksTab.Name = "Monatsübersicht" Then
                lngStart = lngStart + 1
            Else
                lngStart = lngStart + 2
            End If
            ' Zeilen einfügen
            wksTab.Rows(lngStart & ":" & lngStart + lngAnzahl).Insert
        End If
    Next wksTab

Bis später,
Karin