audtofilter 8 spreadsheet ans if criteria is met, copy to the new spreadsheet

[HappyHappyJoyJoy]

Hi guys,

I am new to this forum. I am trying to have a code that allows me to filter 8 worksheets and if the condition is correct, then coy a particular column to a new spreedsheet? I have only started with 1, it filters 1 spreadsheet, but it doesnt copy to the new spreadsheet called "SOI". Why is this the case? Thank you for your help in advance!!!



Sub copyFilteredData()

Dim rng As Range
Dim autofiltrng As Range


With Sheets("General")
.Range("A1").AutoFilter field:=5, Criteria1:="yes", Operator:=xlFilterValues
End With


With ActiveSheet.AutoFilter.Range
On Error Resume Next
Set autofiltrng = .Offset(1, 0).Resize(.Rows.Count–1, 1).SpecialCells(xlCellTypeVisible)
On Error GoTo 0
End With

Worksheets(“SOI”).Range("B5:B22").Cells.Clear

If autofiltrng Is Nothing Then
MsgBox “no data available for copying!”


Else

Set rng = ActiveSheet.AutoFilter.Range

rng.Offset(1, 0).Resize(rng.Rows.Count–1).Copy Destination:=Worksheets("SOI").Range(“A2”)

End If

End Sub

[Stormin']

You need to check which sheet is active when you use the ActiveSheet object. It's best to declare your sheets explicitly.

Try:

Please Login or Register  to view this content.