AW: Filtern Speichern in extra Dateien
17.05.2019 13:24:17
UweD
Hallo
in ein normales Modul...
Modul1
Option Explicit
Sub Denny()
Dim WB, TB, TMP, TBN, Pfad As String, LR As Integer, i As Integer, Datei As String, Ext As String
'*** bescheunigt das Makro
Application.ScreenUpdating = False
Set TB = Sheets("Tabelle1")
Pfad = "x:\Temp\test\"
Ext = ".xlsx"
If TB.AutoFilterMode Then TB.AutoFilterMode = False ' Autofilter ausschalten
'temp. Blatt anlegen
Set TMP = Sheets.Add(After:=Sheets(Sheets.Count))
'copieren
TB.Columns(2).Copy TMP.Columns(1)
With TMP
.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlYes
'sortieren
With .Sort
.SortFields.Clear
.SortFields.Add Key:=Range("A1"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange Range("A:A")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
LR = .Cells(.Rows.Count, "A").End(xlUp).Row 'letzte Zeile der Spalte
End With
With TB
For i = 2 To LR
Datei = TMP.Cells(i, 1)
'filtern
.Range("$B:$B").AutoFilter Field:=1, Criteria1:=Datei
'Neues Blatt
Set TBN = Sheets.Add(After:=Sheets(Sheets.Count))
'Kopieren der gefilterten Daten
.UsedRange.Copy TBN.Cells(1, 1)
'umbenennen
TBN.Name = Datei
'verschieben in eigene Datei
TBN.Move
'Speichern und schließen
With ActiveWorkbook
.SaveAs Filename:=Pfad & Datei & "_" & Format(Date, "YYYY_MM_DD") & Ext, _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
.Close
End With
Next
.AutoFilterMode = False
End With
'temp. Blatt löschen
With Application
.DisplayAlerts = False
TMP.Delete
.DisplayAlerts = True
End With
End Sub
LG UweD