Copy after Autofilter with no rows found
--
Jeff B
"Norman Jones" wrote:
Hi Jeff,
field:=2, _
Should be:
field:=3, _
---
Regards,
Norman
"Norman Jones" wrote in message
...
Hi Jeff,
Try something like:
Public Sub Tester02C()
Dim rng As Range
Dim rng1 As Range
Dim destRng As Range
Dim srcSh As Worksheet
Dim destSh As Worksheet
With ActiveWorkbook
Set srcSh = .Sheets("Index")
Set destSh = .Sheets("Call First")
Set destRng = destSh.Range("A10") '<<=== CHANGE
End With
srcSh.Cells.AutoFilter _
field:=2, _
Criteria1:="CF"
Set rng = srcSh.AutoFilter.Range
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
On Error Resume Next
Set rng1 = rng.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng1 Is Nothing Then
rng1.Copy Destination:=destRng
End If
srcSh.Cells.AutoFilter
End Sub
---
Regards,
Norman
"Jeff B" wrote in message
...
I've run into a quirky situation. I have a row of labels and then my data
below that. I'm using Autofilter to select rows below the labels meeting
certain criteria then copying them to another place. It works fine unless
I
have no rows meeting my criteria. Then I wind up copying the non-filtered
rows instead. Any help would be appreciated. (Range(strEmptyCell finds
the
first empty row to copy to.) I'm using the code below:
Sheets(Index).Select
Sheets(Index).Range("a1").AutoFilter _
field:=3, Criteria1:="CF*"
rngCopyRange.Offset(1, 0).Resize(rngCopyRange.Rows.Count - 1, _
rngCopyRange.Columns.Count).Copy
Sheets("Call First").Activate
Range(strEmptyCell).PasteSpecial (xlPasteValues)
Range("A1").Select
Sheets(Index).Activate
Set rngCopyRange = ActiveCell.CurrentRegion
rngCopyRange.Offset(1, 0).Resize(rngCopyRange.Rows.Count - 1, _
rngCopyRange.Columns.Count).Delete
Range("a1", "C1").AutoFilter 'Filter OFF
--
Jeff B
Thanks, I'll give it a try....
|