Filter and copy question
 

I have the following code to filter a data sheet. How can include a code to
copy the filtered data and place it in a sheet called "Z"?

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsDL = Sheets("DateList")
Set wsO = Sheets("Orders")
Set rngAD = wsO.Range("AllDates")
'update the list of dates
wsDL.Range("A1").CurrentRegion.ClearContents
'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select
rngAD.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=wsDL.Range("A1"), Unique:=True
wsDL.Range("A1").CurrentRegion.Sort _
Key1:=wsDL.Range("A2"), Order1:=xlAscending, header:=xlYes
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("G1:H2"), Unique:=False
End Sub

Filter and copy question
 

I think a quick way to do it would be to select your range after you have
filtered your data and then use:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet("Z").Cells(1,1).PasteSpecial


Hope this helps!


"hoyos" wrote:

I have the following code to filter a data sheet. How can include a code to
copy the filtered data and place it in a sheet called "Z"?

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsDL = Sheets("DateList")
Set wsO = Sheets("Orders")
Set rngAD = wsO.Range("AllDates")
'update the list of dates
wsDL.Range("A1").CurrentRegion.ClearContents
'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select
rngAD.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=wsDL.Range("A1"), Unique:=True
wsDL.Range("A1").CurrentRegion.Sort _
Key1:=wsDL.Range("A2"), Order1:=xlAscending, header:=xlYes
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("G1:H2"), Unique:=False
End Sub

Filter and copy question
 

Thanks Peabrain25 that was useful

"peabrain25" wrote:


I think a quick way to do it would be to select your range after you have
filtered your data and then use:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet("Z").Cells(1,1).PasteSpecial


Hope this helps!


"hoyos" wrote:

I have the following code to filter a data sheet. How can include a code to
copy the filtered data and place it in a sheet called "Z"?

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsDL = Sheets("DateList")
Set wsO = Sheets("Orders")
Set rngAD = wsO.Range("AllDates")
'update the list of dates
wsDL.Range("A1").CurrentRegion.ClearContents
'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select
rngAD.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=wsDL.Range("A1"), Unique:=True
wsDL.Range("A1").CurrentRegion.Sort _
Key1:=wsDL.Range("A2"), Order1:=xlAscending, header:=xlYes
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("G1:H2"), Unique:=False
End Sub

Filter and copy question
 

As far as I recall, in that case, the line:
Selection.SpecialCells(xlCellTypeVisible).Select
is unnecessary - because running copy/paste on a Filtered range will paste -
by default - only the visible cells.
Micky


"peabrain25" wrote:


I think a quick way to do it would be to select your range after you have
filtered your data and then use:

Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheet("Z").Cells(1,1).PasteSpecial


Hope this helps!


"hoyos" wrote:

I have the following code to filter a data sheet. How can include a code to
copy the filtered data and place it in a sheet called "Z"?

Sub ApplyFilter()
Dim wsDL As Worksheet
Dim wsO As Worksheet
Dim rngAD As Range
Set wsDL = Sheets("DateList")
Set wsO = Sheets("Orders")
Set rngAD = wsO.Range("AllDates")
'update the list of dates
wsDL.Range("A1").CurrentRegion.ClearContents
'rngAD.Offset(-1, 0).Resize(rngAD.Rows.Count + 1).Select
rngAD.AdvancedFilter _
Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=wsDL.Range("A1"), Unique:=True
wsDL.Range("A1").CurrentRegion.Sort _
Key1:=wsDL.Range("A2"), Order1:=xlAscending, header:=xlYes
'filter the list
wsO.Range("Database").AdvancedFilter _
Action:=xlFilterInPlace, _
CriteriaRange:=wsO.Range("G1:H2"), Unique:=False
End Sub