Sortierung mit variablem Bereich plus ..
15.10.2012 20:24:10
Norbert
Ich möchte gerne verschiedene Bearbeitungsroutinen in einer Prozedur ablaufen lassen.
Das Tabellenblatt "Zusammenfassung" beinhaltet einen Standardbereich von 10 Tabellenblättern deren Informationen zusammengeführt werden. Danach muss je nach vorangegangener Erfassung verschiedene Anpassungen vorgenommen werden.
1.) Bei mehr als 10 Tabellenblättern muss der Bereich erweitert werden. Die Tabellenblätter können an unterschiedlichen Stellen stehen. Deshalb gibt es ein
Tabellenblatt mit allen Tabellennamen. Vorhandene Berechnungen werden hierbei über
Indirektformeln mit Umschlüsselung über die Zeilennummer ermittelt. Bei mehr als 10
Tabellen muss der Bereich erweitert werden --> Makro Anzahl
2.) Danach soll der Bereich sortiert werden. Danke an die Hilfe von Sheldon und Justin (Beitrag vom 19.09). Ich war leider vier Wochen verhindert. Wusste nicht das der Beitrag nach ca. fünf Tagen geschlossen wird. Ich hoffe Ihr bekommt den Dank über diesen Weg mit. Sortierung funktioniert. Vielen Dank für Eure Hilfe.
3.) Nach der Sortierung wird der Bereich wegen verbundener Zellen neu formatiert.
Dies erfolgt im Makro Formatierung.
Nun die Probleme bzw. offenen Fragen.
A.) Die Routine soll über einen Button gestartet werden. Dies geschieht immer im zu bearbeitendem Tabellenblatt. In Ausnahmefällen kann dies aber einen anderen Namen als Zusammenfassung haben. Müssen in den einzelnen Makros noch Deklarationen vorgenommen werden und wie kann ich den Aufruf über einen variablen Tabellenblattnamen in allen Unterprozeduren ausführen.
B.) Das Einfügen des zusätzlichen Bereichs erfolgt über das Makro Anzahl. Damit der Bildschirm nicht immer aktualisiert wird habe ich vor langer Zeit in einem Makro
ActiveWindow.WindowState = xlMinimized und ActiveWindow.WindowState = xlMaximized
verwendet. Ist dies noch gebräuchlich?
C.) Nachdem eventuelle zusätzliche Bereiche eingefügt worden sind, verändert sich das Tabellenende. Der Bereich A48 bis BG(Tabellenende - aktuell noch BG336) soll sortiert werden. Wie kann ich in den Makros den Sortbereich und die vier Sortierkriterienbereiche variabel anpassen. Dies gilt dann auch für das nachfolgende
Makro Formatierung.
D.) noch offen - In dem variablen Bereich sollen danach die Zeilen 48 Bis Tabellenende gelöscht werden, wenn in der Zelle AR der entsprechenden Zeile nichts steht. Die Zelle wurde vorher über eine Formel mit "" gefüllt. Vor dem Sortieren werden die Formeln in Werte umgewandelt.
E.) Am Schluß möchte ich noch verschiedene Bereiche im oberen Tabellenbereich ausblenden. Zum Beispiel soll nach folgendem Muster der Bereich BH3:BH12 abgefragt werden. Die Zellen BH bis BL sind verbunden. Der Zelleninhalte beinhaltet einen Wert oder "". Wenn der komplette Bereich "" enthält sollen die Zeilen 3 bis 12 ausgeblendent werden.
Sorry für die evtl. zu detailliert dargestellte Aufgabe.
Über Hilfe würde ich mich trotzdem freuen.
Als blutiger VBA-Anfänger bin ich auch für Infos im formalem Aufbau von Makros dankbar.
Nachfolgend die bereits vorhandenen Makros.
Sub MkoZusammenfassung()
' Aufruf der einzelnen Unterprozeduren
ActiveWindow.WindowState = xlMinimized
Application.Run Macro:="MkoAnzahl"
Application.Run Macro:="MkoSortierung"
Application.Run Macro:="MkoFormatierung"
ActiveWindow.WindowState = xlMaximized
End Sub
Sub MkoAnzahl()
'
' Erweiterung des Standardbereiches(A48:BG336 = 10 Tabellenblätter)
' wenn die Anzahl Auslegungen in Zelle BH44 > 0
'
Dim i As Integer
i = Worksheets("Zusammenfassung").Range("BH44").Value
While i > 0
Range("A76:BL104").Select
Selection.Copy
Range("A76:M76").Select
Selection.Insert Shift:=xlDown
ActiveWindow.SmallScroll Down:=-16
i = i - 1
Wend
End Sub
Sub MkoSortierung()
'
' Zusammenfassung der Tabellenblätter - Sortierung nach Typ/Länge/Breite/Pos.Nr.
'
ActiveCell.SpecialCells(xlLastCell).Select
Range("A48:BG336").Select
Range("BD336").Activate
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.UnMerge
ActiveWorkbook.Worksheets("Zusammenfassung").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Zusammenfassung").Sort.SortFields.Add Key:= _
Range("AR48:AR336"), SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:= _
"A; B; 100; C; D; 170" _
, DataOption:=xlSortTextAsNumbers
ActiveWorkbook.Worksheets("Zusammenfassung").Sort.SortFields.Add Key:= _
Range("AB48:AB336"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Zusammenfassung").Sort.SortFields.Add Key:= _
Range("AH48:AH336"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Zusammenfassung").Sort.SortFields.Add Key:= _
Range("R48:R336"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Zusammenfassung").Sort
.SetRange Range("A48:BG336")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub MkoFormatierung()
'
' Übertragung der Original Formatierung (Bereich A42:BG42)
' auf die zusammengefassten Informationen
'
Range("A42:BG42").Select
Selection.Copy
Range("A48:BG336").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub