Mit Button spalten nach bestimmten wert kopieren Zentral

[Niko]

Mit einem Button:

Wenn in Spalte F in den Zeilen “Bestanden“ steht soll die Spalte A, B & F in ein zentrales Blatt kopiert werden, als A,B,C. 
Doch nur die Zeilen die die in Spalte F “Bestanden“ eingetragen haben.
Das über mehr als 40 Arbeitsblätter, damit alle Lehrer aufgenommen werden können.

In den einzelnen Blättern werden die Lehrer Ihre Daten eintragen, möchte danach mit einem Button drücken alle im Arbeitsblatt Zentral übertragen.

Anbei die testmappe: 
  Schueler_Lehrer_auto_kopieren.xlsm (Größe: 16,3 KB / Downloads: 14)
 
Jede Hilfe willkommen, es soll in VBA bewerkstelligt werden, nicht mit Formeln Bitte.
Formeln bei so vielen Lehrern und Schülern kann immer ein Eintrags Fehler passieren, deswegen VBA.
 

Danke im Voraus


Niko der Anfänger

[Werner.M]

Hallo,

zum Beispiel so:

Code:

Public Sub Zusammen()
Dim loSpalte As Long, ws As Worksheet, raFund As Range

Application.ScreenUpdating = False

With Worksheets("Zentral")
    loSpalte = .Cells(2, .Columns.Count).End(xlToLeft).Column
    .Range(.Cells(3, 1), .Cells(35, loSpalte)).ClearContents
End With
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name
        Case "Zentral"
        Case Else
            With ws
                Set raFund = Worksheets("Zentral").Rows(1).Find(what:=.Name, LookIn:=xlValues, lookat:=xlWhole)
                If Not raFund Is Nothing Then
                    If WorksheetFunction.CountIf(.Columns("F"), "Bestanden") > 0 Then
                        .Range("A1:F" & .Cells(.Rows.Count, "A").End(xlUp).Row).AutoFilter field:=6, Criteria1:="Bestanden"
                        With .AutoFilter.Range
                            Union(.Columns("A:B").Offset(1).Resize(.Rows.Count - 1), .Columns("F").Offset(1).Resize(.Rows.Count - 1)).Copy
                            Worksheets("Zentral").Cells(3, raFund.Column).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
                        End With
                        .Range("A1").AutoFilter
                    End If
                End If
            End With
    End Select
Next ws

Set raFund = Nothing
   
End Sub

Wichtig: Die Blattnamen der einzelnen Lehrer müssen exakt so auch in der Zeile 1 des Blattes "Zentral" erfasst sein.

Gruß Werner

[EA1950]

Hallo,

ich habe das Makro mit deiner "Schaltflche 1" verknüpft und die vorhandenen Lehrer-Arbeitsblätter testweise übertragen ins Zentral-Blatt.
Dabei müssen unbedingt die Arbeitsblattnamen (der Lehrer) übereinstimmen mit den Lehrer-Namen in Zeile 1 des Arbeitsblattes "Zentral", damit die Zuordnung funktionieren kann.

Code:

Sub LehrerSchuelerUebertrag()
  Dim WsZ As Worksheet, WsL As Worksheet
  Dim Lehrer As String, SpLehrer As Long, ZlSchZ As Long
  Dim ZlSchQ As Long, Vorname As String, Name As String, Note As String
 
  On Error GoTo Err_Lehrer
  Set WsZ = Worksheets("Zentral")
 
  For Each WsL In Worksheets
    If WsL.Name <> WsZ.Name Then
      Lehrer = WsL.Name
     'Suche Lehrer: Jeweils 1.Spalte im LehrerX
      SpLehrer = WorksheetFunction.Match(Lehrer, WsZ.Rows(1), 0)
      ZlSchZ = 3: ZlSchQ = 2
     
      Do Until IsEmpty(WsL.Cells(ZlSchQ, 1))
        Note = WsL.Cells(ZlSchQ, 6)
        If Note = "Bestanden" Then
          WsZ.Cells(ZlSchZ, SpLehrer) = WsL.Cells(ZlSchQ, 1)
          WsZ.Cells(ZlSchZ, SpLehrer + 1) = WsL.Cells(ZlSchQ, 2)
          WsZ.Cells(ZlSchZ, SpLehrer + 2) = Note
          ZlSchZ = ZlSchZ + 1
        End If
        ZlSchQ = ZlSchQ + 1
      Loop
     
    End If
Nxt_Lehrer:
  Next WsL
  Exit Sub
 
Err_Lehrer:
  MsgBox Prompt:="Das Lehrerblatt '" & Lehrer & "' wurde im Blatt 'Zentral' nicht gefunden." & vbNewLine & _
                 "Dieses Arbeitsblatt wird übergangen.", _
         Title:="Nicht gefundener Lehrer"
  Resume Nxt_Lehrer
 
End Sub

[Niko]

Vorab vielen Dank für deine mühe.
 
Der Code ist mit den Wort Lehrer verbunden, doch es kommen keine Lehrer vor, es werden die Namen der Lehrer in den Arbeitsblättern vorkommen.
 
Aus sich selbsterklärend gründen habe ich deren Namen nicht drauf, deswegen der Lehrer1,2,3,etc. - Sry mein Erklärungsfehler.

Wäre es möglich den code so ausführlich zu führen, das auch ein anfänger wie ich arbeitsblätter sowie spalten/zeilen änderungen selber leicht ändern kann?


Danke
Niko

---

Hallo Werner,
 
 
danke dir auch für Zeit und Mühe.
 
Code wunderschön, kann sogar ein Anfänger wie ich umgehen bzw. ein bisschen verstehen.
 
Doch das Problem ist auch hier bei den Lehrern.
 
Das ist mein Kommunikation Fehler, habe die Lehrer als Lehrer1,2,3,4, etc. deklariert um den Namen nicht erkenntlich machen, doch am Ende wird jedes Arbeitsblatt auch den Namen des Lehrers tragen. Wenn Beispiel der Lehrer 1 - Peter Gans ist wird der Name des Arbeitsblattes Peter Gans benannt.
 
Wenn ein Lehrer wechselt wird auch der Arbeitsblatt Name ändern.
 
Jeder Vorschlag willkommen

 
Danke,
Niko

[RPP63]

Darf ich mal fragen, um welche Art Schule es sich handelt?
Sicherlich um keine, die jemals das Thema Informatik behandelt hat!
Mir fiele jetzt auf Anhieb eine Baumschule ein, nur gibt es dort ja keine Lehrer …
Ich bitte wirklich um Entschuldigung, aber so etwas ist mir in einem Forum noch nicht untergekommen (absolute Hobby-Bastler mal ausgenommen)!
Schon mal etwas von Normalisierung gehört?
Bedacht, wie man eine relationale Datenbank (die hier ja extrem simpel ist) in Excel abbilden kann?
1:n und n:m-Beziehungen?

(und jetzt rege ich mich auch stante pede wieder ab)

[Werner.M]

Hallo,

hälst du mich für geistig minderbemittelt? -Ironie aus-

Auf den Gedanken bin ich tatsächlich auch schon gekommen.

Und dem Code ist das ziemlich wurst wie du die Blätter benennst. Es muss nur sichergestellt sein, dass die Blattnamen identisch mit den Namen in Zeile 1 im Blatt "Zentral" sind - aber das hatte ich dir doch schon geschrieben.

Um bei deinem Beispiel zu bleiben: Wenn das Blatt den Namen Peter Gans hat, dann muss in Zeile 1 vom Blatt "Zentral" halt auch irgendwo Peter Gans stehen.

Ausprobiert hast du das ja wohl nicht.

Gruß Werner

[RPP63]

*Doppelpost, gelöscht*

[Niko]

Um nicht missverstanden zu werden, Lehrer sind nicht allwissend
und schon gar nicht mit Informatik so vertraut wie so manche “IT-Gurus“ es glauben zu wissen.
Dass ich ein Anfänger bin, habe ich nicht von Anfang an verheimlicht.
Wie auch immer, es soll zeit ersparen um ein überblick auf alle Lehrer und Schüller zu haben.
Um zu helfen wo es nötig ist.

Es ist echt schade, wenn man nach Hilfe sucht und solche Kommentare als Hilfe bekommt.
 
Danke,



Niko

---

Zur Info:
Diese Nachricht ist keine Doppelpost oder irgendeine andere Nachricht mit dieser verbunden.

---

Hallo Herr Werner,
 
Sie haben vollkommen recht, es funktioniert.
Komischerweise, funktioniert es bei mir als ich alle Arbeitsblätter mit Option Explicit im VBA Code eingetragen habe. Kann dies möglich sein? …oder ich habe woanders ein Fehler gehabt.
Wie auch immer es funktioniert super, vielen Dank.
Glauben Sie mir es lag mir fern, Sie in irgendeiner Weise zu beleidigen.
Bitte verstehen Sie das ein Anfänger in Excel/VBA nicht nur ein Anfänger in Programmierung ist, er ist auch ein Anfänger in der Frage Stellung.
Wenn ein Anfänger eine gute Fragestellung mit Sicherheit formulieren kann, dann ist er kein Anfänger, das sagt mir meine Lehrer Erfahrung J
 
Danke nochmals und Wünsche Ihnen ein Schönen Tag.
 



Niko
Der Anfänger

[Werner.M]

Hallo,

im Forum duzen wir uns.

Am Option Explicit liegt es sicher nicht. Das ist nur dafür da, dass alle Variablen deklariert werden müssen, sonst meckert VBA. Das lag dann wohl an irgendetwas Anderem.

Und beleidigt fühle ich mich jetzt wirklich nicht - oder ist der Vermerk Ironie aus nicht angekommen.

Gruß Werner

[EA1950]

Hallo,

ich habe mit klarem Deutsch auf den entscheidenden Punkt hingewiesen:
Die Bezeichnungen der Lehrer-Arbeitsblätter (=Aufschrift auf den Arbeitsblatt-Tabulatoren) müssen mit den Bezeichnungen übereinstimmen, die in Zeile 1 des Arbeitsblattes "Zentral" enthalten sind.
Dass sie dies in der Muster-Exceldatei tun, ist selbstverständlich. Sonst hätte ich das geändert. In meinem Makro werden Lehrerarbeitsblätter, deren Namen nicht in Zeile 1 des Blattes "Zentral" enthalten sind, als Fehler in einer Messagebox angezeigt.

Dass du auf diese Übereinstimmung in deiner Echt-Exceldatei achtest und nicht auf sie vergisst, deswegen habe ich auf diesen wichtigen Punkt hingewiesen.
Es muss also (um nur einen Beispielnamen zu nennen) das erste Lehrerarbeitsblatt, das vom Lehrer "Meyer Friedrich" stammt, in der ersten Zeile des Arbeitsblattes "Zentral" auch wieder als "Meyer Friedrich" und nicht als "Lehrer1" aufscheinen. Die Bezeichnungen müssen wirklich exakt übereinstimmen (zB. Klein/Großschreibung, diverse Leerzeichen - am Ende des Namens sollten solche generell entfernt werden!!).

Was das erwähnte "Option Explicit" betrifft: Diese VBA-Codezeile ist als erste Zeile in Codemodulen sehr sinnvoll, sofern dann auch tatsächlich VBA-Code nachfolgt. In Codemodulen, die ansonsten leer sind (abgesehen von dieser einen Zeile), bewirkt diese Zeile nichts.
Ist diese Zeile enthalten, kann der VBA-Compiler überprüfen, ob alle Variablen, die im Code verwendet werden, auch tatsächlich in DIM-Statements (und zwar mit dem passenden Typ) deklariert sind. Man kann dies in den VBA-Editor-Optionen einschalten, sodass diese Option stets in einem neuen Codemodul eingeblendet wird. Leider wird dies von Microsoft als ausgeschaltet vorgegeben, damit auch unerfahrene Programmierer schnell ein paar Zeilen Kraut-und-Rüben-Code schreiben können.
Je länger der enthaltene Code ist, umso schneller wird bei der Programmiertätigkeit klar, wie wichtig diese Option ist - sie kann im Fall eines Fehlers (in Zusammenhang mit Variablen) viel Suchzeit ersparen bzw. überhaupt einen Fehler signalisieren, wo sonst kein solcher angezeigt worden wäre.