Filter - Data - Copy!

A few simple examples, how filtered data are copied. The files at the end of the article are Excelfiles of the version 2003 and 2007. The following code belonged in "Module1"


Ein paar einfache Beispiele, wie gefilterte Daten kopiert werden. Die Dateien am Ende des Artikels sind Exceldateien der Version 2003 und 2007. Der folgende Code gehört in "Modul1"


Option Explicit
Public Sub Step_1()
Dim wksSheetQ As Worksheet
Dim wksSheetZ As Worksheet
Dim rngTMP As Range
On Error GoTo Fin
Application.ScreenUpdating = False
Set wksSheetZ = Sheet1
Set wksSheetQ = Worksheets.Add
With wksSheetQ
.Cells.Clear
Set rngTMP = wksSheetZ.Range("A2"). _
CurrentRegion.SpecialCells(xlCellTypeVisible)
rngTMP.Copy .Range("A1")
End With
Fin:
Set rngTMP = Nothing
Application.ScreenUpdating = True
End Sub
Public Sub Step_3()
Dim wksSheetQ As Worksheet
Dim wksSheetZ As Worksheet
Dim rngTMP As Range
On Error GoTo Fin
Application.ScreenUpdating = False
Set wksSheetZ = Sheet1
Set wksSheetQ = Worksheets.Add
With wksSheetQ
.Cells.Clear
wksSheetZ.Range("A1").AutoFilter Field:=1, _
Criteria1:=wksSheetZ.Range("G1").Value
Set rngTMP = wksSheetZ.Range("A2").CurrentRegion. _
SpecialCells(xlCellTypeVisible)
rngTMP.Copy .Range("A1")
End With
Fin:
Set rngTMP = Nothing
Application.ScreenUpdating = True
End Sub
Public Sub Step_5()
Dim wksSheetQ As Worksheet
Dim wksSheetZ As Worksheet
Dim intTMP As Integer
Dim lngRow As Long
On Error GoTo Fin
Application.ScreenUpdating = False
Set wksSheetZ = Sheet1
Set wksSheetQ = Worksheets.Add
With wksSheetZ
.Range("A1").AutoFilter Field:=1, _
Criteria1:=.Range("G1").Value
For intTMP = 1 To WorksheetFunction.CountA(.Columns(1))
If .Rows(intTMP).Hidden = False Then
lngRow = lngRow + 1
wksSheetQ.Cells(lngRow, 1).Value = _
.Cells(intTMP, 1).Value
End If
Next intTMP
End With
Fin:
Application.ScreenUpdating = True
End Sub
Public Sub Step_2_4_6()
With Sheet1
If .FilterMode Then .ShowAllData
End With
End Sub


Sample 2003

Sample 2007

Kommentare

Beliebte Posts aus diesem Blog

Formeln - auch Array - per VBA eintragen...

Alle Dateien eines Ordners - Optional mit Unterordner

Excel -> Word in Textmarken (Bookmarks)...