Makro für die gesamten Tabellen in der Arbeitsmappe.! Wo ist denn der Fehler ?

Question

Hallo alle miteinander

Ich bin hier im Forum eh auch schon alle Beiträge die irgendwie mit meinen Problem zusammen hängen durch gegangen und getestet aber ich finde den Fehler einfach nicht

Kurzerklärung. Die Datei wird geöffnet -> mit UserForm1.Show -> mit "Ja" bestätigt und mit Sub Mail() soll er den ganzen Code auf jedes TB ausführen. Nur irgendwie macht er das nicht er macht es immer nur für das akutelle TB  wenn ich die Sub Mail() in jedem TB manuell aufrufe dann macht er es 

---

Private Sub Workbook_Open()
UserForm1.Show
End Sub

---

Private Sub CommandButton1_Click()
Application.Run "Mail"
End Sub

---

Private Sub Mail()

Dim Zeile As Long
Zeile = 1
Check (Zeile)  'Sprung in den Aufruf Check()

End Sub

Private Sub Check(ByVal Zeile As Long)
    Dim WS As Worksheet
    Dim Urgenz1 As String
    Dim Urgenz2 As String
    Urgenz1 = "x"
    Urgenz2 = "x"
    Dim i As Long
    
    For Each WS In ThisWorkbook.Worksheets 'alle Tabellenblätter durchlaufen
    
    Const xlUp As Long = &HFFFFEFBE
    Debug.Print CStr(Cells(Rows.Count, 1).End(xlUp).Row) 
      
    For i = Zeile + 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If (CDate(Cells(i, 7).Value) < DateTime.Date) And (CStr(Cells(i, 13).Value) = "x") Then ' Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
        'MsgBox "Email schon geschickt", vbInformation, "Fertig" 'Message Box als Hilfe stellung

        ElseIf (CDate(Cells(i, 7).Value) < DateTime.Date) And (CStr(Cells(i, 13).Value) = vbNullString) Then 'Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
            Cells(i, 13).Value = Urgenz1 'Füge Urgenz1 in Spalte M
            Call Send_Email(i)  'Aufruf Prozedur "Send_Email()"
        End If
        
        If (CDate(Cells(i, 8).Value) < DateTime.Date) And (CStr(Cells(i, 14).Value) = vbNullString) Then 'Wenn Spalte "H" Datum = heutiges Datum & Spalte "N" auf "Leer" Dann
            Cells(i, 14).Value = Urgenz2 'Füge Urgenz2 in Spalte N
            Call Send_Erinnerung(i)  'Aufruf Prozedur "Send_Erinnerung" 
        End If
    Next i
'    Call MsgBox("Fertig!", vbOKOnly)
    Next WS
End Sub

---

Kann mir bitte wer sagen was ich falsch mache und wo der verdammte Fehler ist . Bin echt schon am verzweifeln 

Über dringende Hilfe wäre ich sehr dankbar.

LG SnowWhite

Answer 1

Hallo,

du sprichst zwar mit der Schleife die einzelnen Tabellenblätter an, aber wie du schon gemerkt hast, greifst du nur das aktuelle Tabellenblatt zu.

Nutze WITH um auf die einzelnen Tabellen zuzugreifen und achte auf den Punkt vor den Cells - und Range-Befehlen (damit werden nämlich die Zellen in dem betreffenden Arbeitsblatt angesprochen):

Private Sub Check(ByVal Zeile As Long)
    Dim WS As Worksheet
    Dim Urgenz1 As String
    Dim Urgenz2 As String
    Urgenz1 = "x"
    Urgenz2 = "x"
    Dim i As Long
    
    For Each WS In ThisWorkbook.Worksheets 'alle Tabellenblätter durchlaufen
    
    Const xlUp As Long = &HFFFFEFBE
    'hier nun auf die einzelnen Arbeitsblätter zugreifen
    With WS
       Debug.Print CStr(.Cells(Rows.Count, 1).End(xlUp).Row)
      
       For i = Zeile + 1 To .Cells(Rows.Count, 1).End(xlUp).Row
        If (CDate(.Cells(i, 7).Value) < DateTime.Date) And (CStr(.Cells(i, 13).Value) = "x") Then ' Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
        'MsgBox "Email schon geschickt", vbInformation, "Fertig" 'Message Box als Hilfe stellung

          ElseIf (CDate(.Cells(i, 7).Value) < DateTime.Date) And (CStr(.Cells(i, 13).Value) = vbNullString) Then 'Wenn Spalte "G" Datum = heutiges Datum & Spalte "M" auf "Leer" Dann
            .Cells(i, 13).Value = Urgenz1 'Füge Urgenz1 in Spalte M
            Call Send_Email(i)  'Aufruf Prozedur "Send_Email()"
        End If
        
        If (CDate(.Cells(i, 8).Value) < DateTime.Date) And (CStr(.Cells(i, 14).Value) = vbNullString) Then 'Wenn Spalte "H" Datum = heutiges Datum & Spalte "N" auf "Leer" Dann
            .Cells(i, 14).Value = Urgenz2 'Füge Urgenz2 in Spalte N
            Call Send_Erinnerung(i)  'Aufruf Prozedur "Send_Erinnerung"
        End If
       Next i
'    Call MsgBox("Fertig!", vbOKOnly)
    End With
    
    Next WS
End Sub

Natürlich könnstest du statt mit WITH auch jede Zelle so ansprechen:

WS.Cells(i, 7).Value

Mit WITH sparst du dir die Angabe des Tabellenblattes for jeder Cells- oder Range-Anweisung im entsprechenden WITH-Block.

Gruß

M.O.

Answer 2

Hallo M.O.

Vielen lieben Dank für deine Rückmeldung.

Ich habe beide Varianten probiert nur irgendwie funktioniert das überhaupt nicht.  Bin vielleicht doch zu blöd 

Falls du eine Mail für mich hättest könnte ich dir die ganze Datei schicken (100% sauber) den er fängt in einer TB an wo er gar nichts zu suchen hat 

LG

p.S. Weil im Prinzip eigentlich alles funktioniert nur das "für alle Tabellen" -> nicht funktioniert 

Answer 3

Hallo,

du kannst die Datei hier im Forum hochladen: Anleitung

Gruß

M.O.

Answer 4

Hallo

Ich hoffe es klappt so  Excel

Mail Adresse bei den TB "E-Mail Prof und "E-Mail Assistent" muss zum test noch dazu eingetragen werden^^

GLG

Answer 5

Hallo,

damit das Makro richtig ausgeführt wird, musst du natürlich bei der Ermittlung der letzten beschriebenen Zeile das Tabellenblatt angeben (siehe meinen oben geposteten bearbeiteten Code). Also auch in den folgenden Zeilen:

Debug.Print CStr(WS.Cells(WS.Rows.Count, 1).End(xlUp).Row) ' ermittelt die erste Zelle ohne Wert (formatierte Zellen bleiben unberücksichtigt)

 For i = Zeile + 1 To WS.Cells(Rows.Count, 1).End(xlUp).Row

Das selbe Problem mit den Tabellennamen hast du natürlich auch in den Makros, mit denen du die Mails verschickst. Auch hier werden die Mailadressen immer aus dem aktuellen Tabellenblatt gelesen. Hier müsstest du auch den Namen des gerade bearbeiteten Tabellenblatts mit übergeben und die Cells-Anweisungen anpassen.

Gruß

M.O.

Answer 6

Hallo M.O.

Ich bedanke mich sehr herzlich für deine Rückmeldung.
Ich hab jetzt vor den Cells und Rows  das  "WS." stehen aber funktionieren tut nichts
Aber ich steh irgendwie komplett am Schlauch gerade.

Das selbe Problem mit den Tabellennamen hast du natürlich auch in den Makros, mit denen du die Mails verschickst. Auch hier werden die Mailadressen immer aus dem aktuellen Tabellenblatt gelesen. Hier müsstest du auch den Namen des gerade bearbeiteten Tabellenblatts mit übergeben und die Cells-Anweisungen anpassen.

Wie ? ich muss den Namen des gerade bearbeiteten TB mit übergeben ? Ich verstehs nicht *sorry* 

Wie und wo mache ich denn das ?

LG 

Answer 7

Hallo SnowWhite,

du rufst z.B. dein Makro zum Senden der Erinnerung mit dem Befehl:

Call Send_Erinnerung(i)

Mit dem i in den Klammern übergibst du die Zeile (siehe das betreffende Makro)

Private Sub Send_Erinnerung(ByVal Zeile As Long)

Die übergebene Zeile weist du der Variable iRow zu und liest damit Daten für deine Mail aus:

 iRow = Zeile

..

sEmail_Anrede = Cells(iRow, 9)

Aber die Daten werden aus dem gerade aktiven Tabellenblatt gelesen und nicht aus dem Blatt, das du mit deiner Schleife gerade aufrufst (wie du ja sicher bei den Mails festgestellt hast). Also müsstest du z.B. den Namen des Tabellenblatts genau wie die Zeilennummer beim Aufruf der Mail-Makros mit übergeben und die Cells-Anweisungen in diesen Makros anpassen, damit die Daten für die E-Mails aus den richtigen Tabellenblättern eingelesen werden.

Ich werde die Makros mal anpassen und mich dann wieder melden.

Gruß

M.O.

Answer 8

Hallo M.O. 

Ja genau aus der TB "urliste" werden 1x die neuen Tabellenblätter geniert. Dann soll er im Prinzip nach der Daten Eingabe von den MA ab der Sub Mail() in jedem neuen Tabellenblatt ->  jede Zeile durch gehen und wenn er das TB durch gegangen ist dann das nächste neu generiere TB usw.... Beim durch kompilieren ist mir schon aufgefallen das er irgendwie hin und her hupft in den TB´s und nicht die TB´s nach der Reihe macht 

Aber da ich noch nie VBA Excel programmiert habe sondern eigentlich nur VB bzw VBA Word programmiere bin ich mit den Zellen, Spalten, Blätter, Mappe .....  irgendwie komplett überfordert  

Vor allem weil er nicht in den neu generierten Blätter anfängt sondern in den ersten 5 Tabellenblätter anfängt wo er gar nichts zu suchen hat  

Ich wäre über deine Hilfe und Anpassung wirklich sehr dankbar 

GLG SnowWhite

p.S. das heißt ich müsste die Variable " WS aus der Sub Mail () in die anderen Subs Send_Erinnerung(i) und Send_Email(i) mit übernehmen ?

Answer 9

Hallo SnowWhite,

ich sehe, du hast noch mehr Probleme als nur die Namen der Tabellenblätter. Ich hatte mich schon gewundert, warum alle Blätter durchlaufen werden, aber die Daten - nach meinem Verständnis - ja nur in einigen Blättern stehen.

Habe ich das richtig verstanden, dass das Makro nur die Tabellenblätter druchlaufen soll, die du über die Tabelle "urliste" anlegst? Heißen diese Tabellen immer Liste 1 etc? Oder falls sich die Namen ändern können, bleiben die Namen in Tabellenblatt "urliste" in Spalte A dauerhaft stehen?

Zur Übergabe des Namens:

Den Aufruf kannst du z.B. so machen:

Call Send_Email(i, WS.Name)

Voraussetzung ist natürlich, dass das dies im aufzurufenden Code auch deklariert ist:

Private Sub Send_Email(ByVal Zeile As Long, WsName As String)

Dann kannst du die Daten entsprechend einlesen:

With ThisWorkbook.Worksheets(WsName)
            
            sEmail_Anrede = .Cells(iRow, 9)
            sEmail_Adresse = .Cells(iRow, 11)
            sEmail_Ausgangsdatum = .Cells(iRow, 6)
            sEmail_Bezeichnung = .Cells(iRow, 2)
            sEmail_Liegenschaft = .Cells(iRow, 15)
            
    End With

Gruß

M.O.

Answer 10

Guten Abend M.O. 

Momentan bestehe ich nur noch aus "Problemen"  mit der verdammten VBA Excel Datei ^^

Habe ich das richtig verstanden, dass das Makro nur die Tabellenblätter druchlaufen soll, die du über die Tabelle "urliste" anlegst?

Ja genau das ist richtig 

Heißen diese Tabellen immer Liste 1 etc? Oder falls sich die Namen ändern können, bleiben die Namen in Tabellenblatt "urliste" in Spalte A dauerhaft stehen?

Die Namen ändern sich "Liste1" usw ist jetzt nur ein Test-Text (für mich)   die Namen legen dann die Mitarbeiter fest aus denen dann die TB generiert werden! Aber JA die Namen bleiben im TB "urliste" (hoffe der Name "urliste" wird nicht geändert ) und auf alle Fälle "fix"  in der "Spalte A" dauerhaft.

Genau Ziel ist: Die Datei wird von einer Mitarbeiterin 1 x am Tag aufgerufen dann soll sich quasi über die "Form" mit "JA" bestätigen und dann die Tabellenblätter im jetzigen Testfall "Liste 1, Liste 2, Liste 3 ....." ab der Sub Mail()  der ganze Code in den  TB´s  -> jetztige TB´s ( "Liste 1, Liste 2, Liste 3 .....") durchlaufen werden.  Und dann eben die Mails mit den Daten aus der z.B. TB "Liste 1" weg schicken an die jeweiligen Personen.  (hoffe das war jetzt nicht chinesisch) 

Sorry, Sorry, Sorry ich habe jetzt komplett den Faden verloren . Was soll ich jetzt ändern  ?

Hättest du vielleicht deine geänderte Version für mich ? 
Dann würdest du mich echt retten 

Man ehrlich das bleibt meine erste und letzte VBA Excel Datei 

GLG SnowWhite