Automatisches Aufteilen einer Tabelle in mehrere Tabellenblätter

[Bjoern Mehlhorn]

Hallo,

ich habe die Office 2003-Version zur Verfügung mit Access etc.

Nun suche ich nach einer Funktion, mit der ich quasi wie eine
Pivotfunktion oder eine Autofilterfunktion eine Quelltabelle nach dem
Kriterium einer Spalte automatisch aufteilen kann
D.h. wenn ich z.B. die Buchstaben von A bis Z in einer Spalte habe
(meinetwegen die Anfangsbuchstaben des Nachnamens), dann sollte nach
Anwenden der Funktion 26 Tabellenblätter automatisch entstehen.

In jedem Tabellenblatt sollten dann all die Zeilen sein, auf die das
Aufteilungskriterium in der entsprechenden Spalte zutraf (d.h. hier
z.B. sind alle Müllers, Meiers und Mehlhorns im Tabellenblat M
gelandet, alle Abrahams, Alberts, Augenthalers etc. im Blatt A).

Vielleicht kann man dann sogar die Blätter in 26 einzelne
Excel-Tabellen bringen (A.xls, B.xls u.s.w.).

Sollte eine Ausprägung der Variable (z.B. Buchstabe) nicht enthalten
sein, braucht er auch nicht angelegt zu werden - ähnlich der
Funktionsweise einer Pivot-Tabelle bzw. einer Auto-Filter-Funktion.

Wie krieg ich diese Split-Funktion hin?


Gruß

Björn

[Alexander Wolff]

Post by Bjoern Mehlhorn
ich habe die Office 2003-Version zur Verfügung mit Access etc.
Nun suche ich nach einer Funktion, mit der ich quasi wie eine
Pivotfunktion oder eine Autofilterfunktion eine Quelltabelle nach dem
Kriterium einer Spalte automatisch aufteilen kann
D.h. wenn ich z.B. die Buchstaben von A bis Z in einer Spalte habe
(meinetwegen die Anfangsbuchstaben des Nachnamens), dann sollte nach
Anwenden der Funktion 26 Tabellenblätter automatisch entstehen.
In jedem Tabellenblatt sollten dann all die Zeilen sein, auf die das
Aufteilungskriterium in der entsprechenden Spalte zutraf (d.h. hier
z.B. sind alle Müllers, Meiers und Mehlhorns im Tabellenblat M
gelandet, alle Abrahams, Alberts, Augenthalers etc. im Blatt A).
Vielleicht kann man dann sogar die Blätter in 26 einzelne
Excel-Tabellen bringen (A.xls, B.xls u.s.w.).
Sollte eine Ausprägung der Variable (z.B. Buchstabe) nicht enthalten
sein, braucht er auch nicht angelegt zu werden - ähnlich der
Funktionsweise einer Pivot-Tabelle bzw. einer Auto-Filter-Funktion.
Wie krieg ich diese Split-Funktion hin?

Ein aufgezeichneter Autofilter (benutzerdefiniert LINKS(A2;1)="A" bis "Z")
sollte es eigentlich bringen. Das Filtrat wird in ein neues Blatt kopiert
und mit dem Buchstaben benannt. Das neue Blatt kann auch als neue Datei
A.xls gespeichert werden.

Nach der Aufzeichnung von "A" baust Du in den Code selbst die entsprechenden
Schleifen ein, damit das bis "Z" durchläuft.

[Bjoern Mehlhorn]

Post by Alexander Wolff

Post by Bjoern Mehlhorn
Nun suche ich nach einer Funktion, mit der ich quasi wie eine
Pivotfunktion oder eine Autofilterfunktion eine Quelltabelle nach dem
Kriterium einer Spalte automatisch aufteilen kann
In jedem Tabellenblatt sollten dann all die Zeilen sein, auf die das
Aufteilungskriterium in der entsprechenden Spalte zutraf

Ein aufgezeichneter Autofilter (benutzerdefiniert LINKS(A2;1)="A" bis "Z")
sollte es eigentlich bringen. Das Filtrat wird in ein neues Blatt kopiert
und mit dem Buchstaben benannt. Das neue Blatt kann auch als neue Datei
A.xls gespeichert werden.
Nach der Aufzeichnung von "A" baust Du in den Code selbst die entsprechenden
Schleifen ein, damit das bis "Z" durchläuft.

Das wäre für feste Split-Kriterien (A-Z) noch möglich.

Bei Autofilter bzw. einer Pivot-Funktion ist dem Programm aber vorher
noch nicht klar, wonach es aufteilen soll und wieviele verschiedene
Aufteilungsmöglichkeiten existieren.

Wenn ich nun z.B. nach Postleitzahl trennen möchte, würde ich bei
einem Durchlauf auch nicht gern 10.000 Dateien haben, wovon die
meisten wohl leer sind, sondern nur ca. 20 Dateien von all denen, die
gefüllt sind.

Das Programm sollte also irgendwie alle Ausprägungen des Merkmales
sich ansehen können und dann sooft durchlaufen mit jeder Ausprägung
1x, daß ich soviele Blätter habe wie Ausprägungen des Merkmals.

Besteht da eine Chance?

Gruß

Björn

[Claus Busch]

Hallo Björn,

Post by Bjoern Mehlhorn
Das wäre für feste Split-Kriterien (A-Z) noch möglich.
Bei Autofilter bzw. einer Pivot-Funktion ist dem Programm aber vorher
noch nicht klar, wonach es aufteilen soll und wieviele verschiedene
Aufteilungsmöglichkeiten existieren.
Wenn ich nun z.B. nach Postleitzahl trennen möchte, würde ich bei
einem Durchlauf auch nicht gern 10.000 Dateien haben, wovon die
meisten wohl leer sind, sondern nur ca. 20 Dateien von all denen, die
gefüllt sind.
Das Programm sollte also irgendwie alle Ausprägungen des Merkmales
sich ansehen können und dann sooft durchlaufen mit jeder Ausprägung
1x, daß ich soviele Blätter habe wie Ausprägungen des Merkmals.

probiers mal so:
Sub Aufteilen()
'21/10/2006
'© Claus Busch

Dim myRange As Range
Dim i As Integer
Dim Finden As Integer
Dim Anfang As String



For i = 65 To 90
Anfang = Chr(i)

'Hier deinen Tabellennamen anpassen
With Sheets("Vorlage")
'Hier deinen Bereich anpassen
Set myRange = .Range("A2:A1000")
'Hier dein Autofilterfeld anpasssen
.Range("A1").AutoFilter Field:=1, Criteria1:=Anfang & "*"

Finden = Application.WorksheetFunction.Subtotal(3, myRange)
If Finden >= 1 Then

Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Anfang

.Range("A1", .Cells(1, 1).SpecialCells(xlLastCell)). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets(Anfang).Range("A1")

End If
End With
Next i

End Sub

[Bjoern Mehlhorn]

Hallo Claus:

super toll funktioniert das mit den Buchstaben - von A bis Z!

Phantastisch - ich bin begeistert.

Ich habe leider nur das Problem, daß sich die Tabelle nach anderen
Merkmalen aufteilen muß als nach A-Z - die sind vom Inhalt der Spalte
vorgegeben und hat ca. 70 Ausprägungen (s.u.).

Post by Claus Busch
Sub Aufteilen()
'21/10/2006
'© Claus Busch
Dim myRange As Range
Dim i As Integer
Dim Finden As Integer
Dim Anfang As String
For i = 65 To 90
Anfang = Chr(i)

Hier wird ja auf Ascii 65 bis Ascii 90 abgehoben, so wie ich das
verstehe.

Post by Claus Busch
'Hier deinen Tabellennamen anpassen
With Sheets("Vorlage")
'Hier deinen Bereich anpassen
Set myRange = .Range("A2:A1000")
'Hier dein Autofilterfeld anpasssen

Muß hier jedes mal exakt das Ende meiner Liste (A1000) stehen, oder
kann da prophylaktisch auch A50000 stehen?

Post by Claus Busch
.Range("A1").AutoFilter Field:=1, Criteria1:=Anfang & "*"
Finden = Application.WorksheetFunction.Subtotal(3, myRange)
If Finden >= 1 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Anfang
.Range("A1", .Cells(1, 1).SpecialCells(xlLastCell)). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets(Anfang).Range("A1")
End If
End With
Next i
End Sub

Die Ausprägungen in meiner Tabelle, nach der dann die Einzel-Tabellen
entstehen sollten, sehen ungefähr so aus:

B
BNAFDA
BB1
BB2
BNB2
N3
NNF3FG
RKL3SA
O
P5
R7
SNAG
VVMM
WA
Z

Es sind Abkürzungen aus Großbuchstaben und Zahlen - keine
Sonderzeichen - zwischen 1 und 6 Zeichen lang - kommen alle aus einem
definierten Wertevorrat von ca. 70 Abkürzungen.

Wie kann ich nun nach diesen Abkürzungen aus einer bekannten Tabelle
statt nach 26 Buchstaben aufteilen. Ggfs. kann sich Excel über eine
Pivottabelle den nächsten zutreffenden Wert heraussuchen oder die
Wertetabelle vorab erstellen.


Besteht die Möglichkeit, die Tabellenblätter dann schnell
automatisiert in Einzel-Tabellen umzuspeichern?


Gruß

Björn

[Claus Busch]

Hallo Björn,

Post by Bjoern Mehlhorn
super toll funktioniert das mit den Buchstaben - von A bis Z!
Phantastisch - ich bin begeistert.
Ich habe leider nur das Problem, daß sich die Tabelle nach anderen
Merkmalen aufteilen muß als nach A-Z - die sind vom Inhalt der Spalte
vorgegeben und hat ca. 70 Ausprägungen (s.u.).

Post by Claus Busch
Sub Aufteilen()
'21/10/2006
'© Claus Busch
Dim myRange As Range
Dim i As Integer
Dim Finden As Integer
Dim Anfang As String
For i = 65 To 90
Anfang = Chr(i)

Hier wird ja auf Ascii 65 bis Ascii 90 abgehoben, so wie ich das
verstehe.

ja, so ist es. So hatte ich deine Frage verstanden.

Post by Bjoern Mehlhorn

Post by Claus Busch
'Hier deinen Tabellennamen anpassen
With Sheets("Vorlage")
'Hier deinen Bereich anpassen
Set myRange = .Range("A2:A1000")
'Hier dein Autofilterfeld anpasssen

Muß hier jedes mal exakt das Ende meiner Liste (A1000) stehen, oder
kann da prophylaktisch auch A50000 stehen?

du könntest das vorsorglich so machen, aber das kostet wieder Rechenzeit.
Du könntest auch deine letzte Zeile ermitteln lassen und den exakten
Bereich durchsuchen.

Post by Bjoern Mehlhorn

Post by Claus Busch
.Range("A1").AutoFilter Field:=1, Criteria1:=Anfang & "*"
Finden = Application.WorksheetFunction.Subtotal(3, myRange)
If Finden >= 1 Then
Worksheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Anfang
.Range("A1", .Cells(1, 1).SpecialCells(xlLastCell)). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets(Anfang).Range("A1")
End If
End With
Next i
End Sub

Die Ausprägungen in meiner Tabelle, nach der dann die Einzel-Tabellen
B
BNAFDA
BB1
BB2
BNB2
N3
NNF3FG
RKL3SA
O
P5
R7
SNAG
VVMM
WA
Z
Es sind Abkürzungen aus Großbuchstaben und Zahlen - keine
Sonderzeichen - zwischen 1 und 6 Zeichen lang - kommen alle aus einem
definierten Wertevorrat von ca. 70 Abkürzungen.
Wie kann ich nun nach diesen Abkürzungen aus einer bekannten Tabelle
statt nach 26 Buchstaben aufteilen. Ggfs. kann sich Excel über eine
Pivottabelle den nächsten zutreffenden Wert heraussuchen oder die
Wertetabelle vorab erstellen.
Besteht die Möglichkeit, die Tabellenblätter dann schnell
automatisiert in Einzel-Tabellen umzuspeichern?

Eine Möglichkeit wäre die von dir schon angesprochene Pivot-Tabelle. Deine
Abkürzungen könntest du dabei in das Seitenfeld ziehen. Falls deine
Abkürzungen nicht extra in einer Spalte stehen. müsste man mal sehen, wie
man sie aus dem Text extrahieren könnte. Wenn du dann die Pivot-Tabelle
hast, kannst du einen Rechtsklick auf das Seitenfeld machen und "Seiten
anzeigen" wählen. Dann werden diese Seiten mit dem entsprechenden Namen
angelegt.

[Claus Busch]

Hallo Björn,

unter http://www.claus-busch.de/TblBlattSplitten.xls habe ich mal eine
Test-Tabelle hinterlegt. Die Suchstrings sind in Spalte AA und können nach
deinen Wünschen angepasst oder auch verschoben werden. Beim Verschieben
muss dann allerdings der Code entsprechend angepasst werden. Durchsucht
wird jetzt Spalte A mit ihrer exakten Ausdehnung. Durch Ausführen des
Makros TablSplitten kannst du sehen, ob du damit etwas anfangen kannst.
Falls nicht, geht immer noch die Version mit der Pivot-Tabelle.

[Dr Bjoern Mehlhorn]

Hallo Claus,

Post by Claus Busch
unter http://www.claus-busch.de/TblBlattSplitten.xls habe ich mal eine
Test-Tabelle hinterlegt. Die Suchstrings sind in Spalte AA

Meint also nach VB-Interpretation die "ROW 27"???

Post by Claus Busch
und können nach
deinen Wünschen angepasst oder auch verschoben werden.

Ich würde dann also in Spalte 27 einfach eine Kopie der
Pivot-Auswertung meiner Tabelle reinkopieren?

Post by Claus Busch
Beim Verschieben
muss dann allerdings der Code entsprechend angepasst werden. Durchsucht
wird jetzt Spalte A mit ihrer exakten Ausdehnung.

D.h. in Spalte A muß dann der String drin sein, nach dem ich die ganze
Tabelle aufteilen möchte.

Post by Claus Busch
Durch Ausführen des
Makros TablSplitten kannst du sehen, ob du damit etwas anfangen kannst.

Gigantisch - teilt sich alles automatisch auf.

Hoffentlich nur die ersten 26 Spalten... Sonst ist ja in Spalte 27
(AA) immer ein Teil der Pivottabelle zu sehen...
Aber das würde auch nix machen - das kann ja jeder sehen.

Nur wo tue ich jetzt hier in dieser Variante die Zahl der Zeilen
reinschreiben, die ich auswerten möchte (immer alle)? Oder ist das
nicht mehr nötig, das zu sagen? So recht blicke ich durch den Code
nicht durch.

Post by Claus Busch
Falls nicht, geht immer noch die Version mit der Pivot-Tabelle.

Verstehe nicht... ???


Gruß

Björn

[Claus Busch]

Hallo Björn,

Post by Dr Bjoern Mehlhorn
Hallo Claus,

Post by Claus Busch
unter http://www.claus-busch.de/TblBlattSplitten.xls habe ich mal eine
Test-Tabelle hinterlegt. Die Suchstrings sind in Spalte AA

Meint also nach VB-Interpretation die "ROW 27"???

nein Column(Spalte) 27

Post by Dr Bjoern Mehlhorn

Post by Claus Busch
und können nach
deinen Wünschen angepasst oder auch verschoben werden.

Ich würde dann also in Spalte 27 einfach eine Kopie der
Pivot-Auswertung meiner Tabelle reinkopieren?

ja, oder wenn du diese Strings schon so vorliegen hast in deiner Tabelle,
könntest du sie mit Spezialfilter und keine Duplikate dort hin filtern.

Post by Dr Bjoern Mehlhorn

Post by Claus Busch
Beim Verschieben
muss dann allerdings der Code entsprechend angepasst werden. Durchsucht
wird jetzt Spalte A mit ihrer exakten Ausdehnung.

D.h. in Spalte A muß dann der String drin sein, nach dem ich die ganze
Tabelle aufteilen möchte.

nein, er muss nicht drin sein. Stimmt in Spalte A kein String mit den
hinterlegten in AA ein, werden einfach keine Blätter angelegt.

Post by Dr Bjoern Mehlhorn

Post by Claus Busch
Durch Ausführen des
Makros TablSplitten kannst du sehen, ob du damit etwas anfangen kannst.

Gigantisch - teilt sich alles automatisch auf.
Hoffentlich nur die ersten 26 Spalten... Sonst ist ja in Spalte 27
(AA) immer ein Teil der Pivottabelle zu sehen...
Aber das würde auch nix machen - das kann ja jeder sehen.

du könntest Spalte AA auch ausblenden, dann wird sie nicht erfasst.

Post by Dr Bjoern Mehlhorn
Nur wo tue ich jetzt hier in dieser Variante die Zahl der Zeilen
reinschreiben, die ich auswerten möchte (immer alle)? Oder ist das
nicht mehr nötig, das zu sagen? So recht blicke ich durch den Code
nicht durch.

das mit den Zeilen ist doch im Code schon geregelt. LRow ist die letzte
Zeile deiner Tabelle. LRow1 ist die letzte Zeile der Spalte AA. Du kannst
also jederzeit deine Tabelle oder die Suchbegriffe erweitern oder teilweise
löschen. Es funktioniert immer.

Post by Dr Bjoern Mehlhorn

Post by Claus Busch
Falls nicht, geht immer noch die Version mit der Pivot-Tabelle.

Verstehe nicht... ???

in meinem anderen Posting hatte ich doch die Erstellung einer Pivot-Tabelle
beschrieben. Wenn du dann die Spalte mit deinen Abkürzungen in das
Seitenfeld der Pivot-Tabelle rein bringst, kannst du einfach durch
Rechtsklick auf das Seitenfeld und Seiten anzeigen, ebenso deine Tabelle
aufsplitten wie hier mit dem Code.
Falls du nicht klar kommst, kannst du dich gerne wieder melden.