Erst Filtern+Kopieren, dann Urzustand
Dani
Ich habe folgenden Code, der zunächst die Daten nach den Kriterien filtert und dann kopiert. Das Problem an der Sache ist, das die Ausgangstabelle dann auch im gefilterten Zustand ist.
Sub FilternKopierenTGA()
Dim rngAct As Range
With Worksheets("TGA")
.Cells.Clear
Range("N1").AutoFilter _
Field:=14, Criteria1:="TGA" 'Filtern Spalte N nach TGA
Range("M1").AutoFilter _
Field:=15, Criteria1:="26000"
Set rngAct = Range("A1").CurrentRegion
rngAct.Columns(1).SpecialCells(xlCellTypeVisible).Copy .Range("A1")
rngAct.Columns(2).SpecialCells(xlCellTypeVisible).Copy .Range("B1")
rngAct.Columns(4).SpecialCells(xlCellTypeVisible).Copy .Range("C1")
rngAct.Columns(7).SpecialCells(xlCellTypeVisible).Copy .Range("D1")
rngAct.Columns(9).SpecialCells(xlCellTypeVisible).Copy .Range("E1")
rngAct.Columns(10).SpecialCells(xlCellTypeVisible).Copy .Range("F1")
rngAct.Columns(12).SpecialCells(xlCellTypeVisible).Copy .Range("G1")
rngAct.Columns(13).SpecialCells(xlCellTypeVisible).Copy .Range("H1")
rngAct.Columns(14).SpecialCells(xlCellTypeVisible).Copy .Range("I1")
rngAct.Columns(15).SpecialCells(xlCellTypeVisible).Copy .Range("J1")
rngAct.Columns(16).SpecialCells(xlCellTypeVisible).Copy .Range("K1")
rngAct.Columns(28).SpecialCells(xlCellTypeVisible).Copy .Range("L1")
rngAct.Columns(29).SpecialCells(xlCellTypeVisible).Copy .Range("M1")
rngAct.Columns(31).SpecialCells(xlCellTypeVisible).Copy .Range("N1")
rngAct.Columns(32).SpecialCells(xlCellTypeVisible).Copy .Range("O1")
rngAct.Columns(34).SpecialCells(xlCellTypeVisible).Copy .Range("P1")
rngAct.Columns(36).SpecialCells(xlCellTypeVisible).Copy .Range("Q1")
Sheets("TGA").Range("1:1").AutoFilter
End With
End Sub
Vielen Dank schon mal an alle
Dani