410 Aufrufe
Gefragt in Windows 10 von
Hallo miteinander,

ich stecke noch etwas in Kinderschuhen mit VBA, habe durch googlen schon ein paar Dinge heraussgefunden. Jedoch bin ich auf der Suche nach einer einfachen Abfrage. Folgendes:

alle paar Wochen bekomme ich mehrere aktualisierte Listen mit Namen, Vornamen und Klasse. Ich würde gerne eine VBA Abfrage machen, die 2-3 Tabellenblätter durchsucht, und eine aktualisierte Liste in einer Tabelle erstellt.

Die Tabellen sehen wie folgt aus:

Spalte B - Name

Spalte C - Vorname   

Spalte G - Klasse

das Selbe gibt es dann noch auf einem weiteren Tabellenblatt.

Jetzt sollte die Abfrage alle nötigen Tabellenblätter durchsuchen, und auf einem neuen Tabblatt eine Tabelle erstellen, sortiert nach Prio 1: Klasse, dann Nachname, dann Vorname

Jedoch hätte ich gerne in folgender Formatierung:

Spalte B - Klasse

Spalte C - Name

Spalte D - Vorname

Wäre ein Traum, wenn mir da jemand helfen könnte, sonst ist das jedesmal ein wenig mühsam.

Liebe Grüße Andy

13 Antworten

0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)

Hallo Andy,

kopiere das folgende Makro in ein Standard-Modul deiner Arbeitsmappe. Bevor du das Makro ausprobierst, musst die Namen der Tabellen, die in der betreffenden Arbeitsmappe sind, im Makro anpassen. Wenn z.B. alle Tabelle in der Arbeitsmappe durchlaufen werden sollen, kann man das entsprechend einfacher machen.

Für die Liste wird automatisch ein neues Tabellenblatt angelegt.

Sub liste()
Dim strListe As String
Dim arrDaten As Variant
Dim arrTabellen As String
Dim t As Long
Dim i As Long
Dim lngLetzte As Long
Dim lngZeile As Long
Dim bExists As Boolean
Dim Antwort

'Tabellenblätter definieren, aus denen die Gesamtliste erstellt werden soll
'Namen anpassen
arrListe = Array("Tabelle2", "Tabelle3")

'Name für Übersichtsliste erstellen
If Month(Now) < 10 Then
  strName = "Liste " & Year(Now) & "-0" & Month(Now) & "-" & Day(Now)
 Else
  strName = "Liste " & Year(Now) & "-" & Month(Now) & "-" & Day(Now)
End If

'Prüfen, ob Tabellenblatt für Übersichtsliste bereits besteht
For i = 1 To ThisWorkbook.Worksheets.Count
  If ThisWorkbook.Worksheets(i).Name = strName Then
    'Marker, dass Tabellenblatt existiert
    bExists = True
    'Schleife verlassen
    Exit For
  End If
Next i
 
'Falls Tabellenblatt noch nicht existiert, dann Tabellenblatt erstellen
If bExists = True Then
  'Fragen was passieren soll
  Antwort = MsgBox("Achtung! Die Tabelle " & strName & " existiert bereits! Soll die Liste neu erstellt werden?", 36, "Liste existiert schon")
  If Antwort = vbNo Then
       'Makro beenden, falls liste nicht neu erstellt werden soll
       Exit Sub
    Else
        Application.DisplayAlerts = False             'keine Nachfrage anzeigen
        Worksheets(strName).Delete                    'vorhandenes Arbeitsblatt löschen
        Application.DisplayAlerts = True              'Nachfrage wieder aktivieren
  End If
End If
        
Worksheets.Add Before:=Worksheets(1)          'Arbeitsblatt vor dem ersten Blatt einfügen
With ActiveSheet
  .Name = strName                    'Arbeitsblatt benennen
  'Überschriften erstellen
  .Range("B1") = "Klasse"
  .Range("C1") = "Name"
  .Range("D1") = "Vorname"
  'Überschriften formatieren
  With .Range("B1:D1")
     .Font.Bold = True   'Fett
     .HorizontalAlignment = xlCenter  'zentriert
  End With
End With
   
'erste Einfügezeile für die Liste festlegen
lngZeile = 2

'Tabellenblätter mit den zu kopierenden Daten durchlaufen
For t = 0 To UBound(arrListe)               'Zählung fängt hier bei Null an
  With ThisWorkbook.Worksheets(arrListe(t))
    lngLetzte = .Cells(Rows.Count, 1).End(xlUp).Row
    'Array Redimensionieren
    ReDim arrDaten(lngLetzte - 1, 7)  'ohne 1. Zeile und Spalten A - G
    'Daten aus Tabellenblatt einlesen
    arrDaten = .Range("A2:G" & lngLetzte)
  End With
  'Daten in Gesamtliste eintragen
  With ThisWorkbook.Worksheets(strName)
    'Array mit Daten aus Arbeitsblatt durchlaufen
    For i = 1 To UBound(arrDaten, 1)
      .Cells(lngZeile, 2) = arrDaten(i, 7)    'Spalte B = Klasse
      .Cells(lngZeile, 3) = arrDaten(i, 2)    'Spalte C = Name
      .Cells(lngZeile, 4) = arrDaten(i, 3)    'Spalte D = Vorname
      lngZeile = lngZeile + 1                 'Zähler für Einfügezeile erhöhen
    Next i
  End With
Next t

'Nun die Daten sortieren
With ThisWorkbook.Worksheets(strName)
  .Sort.SortFields.Clear
  .Sort.SortFields.Add Key:=Range("B2:B" & lngZeile - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Sort.SortFields.Add Key:=Range("C2:C" & lngZeile - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
  .Sort.SortFields.Add Key:=Range("D2:D" & lngZeile - 1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
   With .Sort
        .SetRange Range("B1:D" & lngZeile - 1)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With

End Sub

Gruß

M.O.

0 Punkte
Beantwortet von
Hallo M.O.

enstschuldige die lange Zeit meiner Rückmeldung. Grandios was du gemacht hast. Vielen Dank.

Eine Frage bzw. Ergänzung bräuchte ich noch:

Ich bräuchte eine Abfrage die ich konfigurieren kann und zwar:

aus besagten Listen soll eine neue erstellt werden, wenn in Spalte "I", bzw. "J", bzw "K" bzw. "L". bzw. "M" ein "A" oder "B" oder "B+" oder "G" (oder Kombinationen aus allem) steht.

Also der Sinn ist, Kinder melden sich für Baustein "A" am Montag (Spalte "I") an, diese sind in einer langen Liste gemischt mit allen anderen Infos. Jetzt will ich aus dieser Liste eine Liste erstellen, mit allen "A"-Kindern Montags. Das natürlich dann auch mit Baustein "B" etc.

Wäre ein Traum, wenn du mir dabei nochmal helfen könntest.

Gruß Andy
0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)

Hallo Andy,

könntest du mal eine kleine Beispieldatei - natürlich ohne echte Daten - zur Verfügung stellen, in der du auch vielleicht mal darstellst, wie das Ergebnis aussehen soll? Wie du eine Beispieldatei hier hochladen kannst, kannst du hier nachlesen: Beschreibung

Gruß

M.O.

0 Punkte
Beantwortet von

https://supportnet.de/forum/?qa=blob&qa_blobid=681031268572918950

Also folgende zwei Funktionen benötigen ich:

1. einmal aus beiden Tabellenblättern kombiniert eine automatische Erstellung neuer Listen nach Klassen. Also je Klassenstufe eine Tabelle (sortiert nach Vornamen).

2. aus der Tabelle "GTS Betr. (2022-23)" eine Abfrage aller Kinder, die z.B. montags (Spalte "I") Baustein B ("B") gebucht haben. das für jeden Tag einzeln, und jede Bausteinkombination (A, B, AB, B+, G, GB, etc.)

Ich hoffe, das ist so etwas klarer. Ich dank dir schon mal!

Gruß Andy

0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)

Hallo Andy,

hier ist deine bearbeitete Beispielmappe: Download

In der Mappe findest du auch eine kurze Erklärung. Schau mal, ob das so für dich in Ordnung ist.

Gruß

M.O.

0 Punkte
Beantwortet von
Hi M.O.,

vielen Dank für die Datei und die Codes. Das ist mega. Folgendes krieg ich leider nicht angepasst mit meinem rudimentären VBA Wissen:

Bei "Klassenlisten" kopiert er mir zuviele Daten in die Extra Tab-Blätter. Ich bräuchte nur:

Spalte A: laufende Nummerierung

Spalte B: Vorname

Spalte C: Nachname

Das Ganze möglichst nach Vorname sortiert.

Adresse und Hausnummer brauch ich nicht.

____

Der Baustein_Code ist auch genau das, was ich brauche - bis auf eine kleine Änderung (wenn möglich). Ich hatte mich glaub doof ausgedrückt. Also was ich letzten Endes brauche ist:

Ein Tab-Blatt mit allen Kindern die Montags "A" enthalten, egal, ob es eine AB oder ähnliche Kombination gibt. Also ein Kind bucht Baustein A und B am Montag. Das heißt, es kommt vor UND nach der Schule, deshalb brauch ich dieses Kind auf der Liste mit Baustein A UND einer Liste mit Baustein B am Montag. Eine separate Liste mit AB brauch ich nicht. Das hatte ich falsch formuliert. Sorry.

Ich versuch es nochmal

Tab.Blatt:

"Montag A": alle Kinder, die montags A enthalten

"Montag B": alle Kinder, die montags B enthalten

"Dienstag A":

"Dienstag B":

"Mittwoch A"

"Mittwoch B"

"Donnerstag A"

"Donnerstag B"

"Donnerstag G": Da alle Kinder, die donnerstags G enthalten, egal, ob AG oder BG oder AGB Kombinationen

"Freitag A"

"Freitag B": Freitags ist noch die Besonderheit, dass zu den Kindern die B haben, auch die B+ Kinder zusätzlich auf die Liste kommen. Ist es möglich die "B+" Kinder mit fetter Schrift zu markieren? oder einem Sternchen im Namen?

Und als letztes (sorry): Auch dort brauch ich nur folgende Daten:

Spalte A: laufende Nummerierung

Spalte B: Vorname (danach sortiert)

Spalte C: Nachname

Spalte D: Klasse

ok, ich merke, das sind doch nochmal ein paar Dinge. Ich will auch gar nicht zuviel verlangen. Es würde die Arbeit nur so viel einfacher machen. Danke auf jeden Fall schon mal für all deine Bemühungen!!!

digitale Grüße

Andy
0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)
Hallo Andy,

das mit den Klassenlisten ist schnell erledigt.

Noch einmal zu den Blättern mit den Bausteinen. Habe ich das richtig verstanden, dass die Daten in die folgenden Tabellenblätter kopiert werden sollen:

- Tabellenblatt für Montag (vor Schule) mit A, AB

- Tabellenblatt für Montag (nach Schule) mit B, AB

- Tabellenblatt für Dienstag mit A, AB

- Tabellenblatt für Dienstag mit B, AB

- Tabellenblatt für Mittwoch mit A, AB

- Tabellenblatt für Mittwoch mit B, AB

- Tabellenblatt für Donnerstag mit A, AB

- Tabellenblatt für Donnerstag mit B, AB

- Tabellenblatt für Donnerstag G, GA, GB

- Tabellenblatt für Freitag mit A, AB, AB+

- Tabellenblatt für Freitag mit B, B+, AB+

Man kann die Kinder mit einem +Baustein auch markieren.

Gruß

M.O.
0 Punkte
Beantwortet von

Hallo M.O.

Also es ist alles richtig. Donnerstags und Freitags ergänze ich noch folgendes (fettgedrucktes) - kann sein, dass es diese Kombis aktuell nicht gibt, aber die Liste aktualisiert sich ständig und es kommt ein neues Schuljahr, deshalb wären dann alle möglichen Kombinationen abgefragt.

- Tabellenblatt für Donnerstag mit A, AB, ABG,

- Tabellenblatt für Donnerstag mit B, AB, GB, ABG

- Tabellenblatt für Donnerstag G, GA, GB, ABG

- Tabellenblatt für Freitag mit A, AB, AB+

- Tabellenblatt für Freitag mit B, B+, AB+, AB

Kannst du mir markieren, an welcher Stelle im Code du diese Kombis angiebst? dann kann ich die zur Not selber korrigieren oder ergänzen (falls es mal neue Bausteine gibt)

Wieder mal ein riesen Dankeschön!

Gruß Andy

0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)

Hallo Andy,

ich schreibe dir noch etwas Erläuterung in die Beispieldatei wink.

Gruß

M.O.

0 Punkte
Beantwortet von m-o Profi (22.8k Punkte)

Hallo Andy,

hier die angepasste Tabelle: Download

Schau mal, ob das jetzt so funktioniert, wie du willst.

Gruß

M.O.

...