Excel VBA, filter/copy/past to new worksheet

[chyno813]

I received a raw data weekly, the data has 5 worksheets and I need to filter the data start from "B4" cell with criteria "I" then copy and past all result from 5 worksheet into one new file. can someone help me with more efficient way?

 

[Michael M]

This will copy all filtered data into a sheet called "Compiled Master"
It assumes data is in Columns "A" to "H"....change the line in red to suit

Sub MM3()
Dim rang As Range, sh As Worksheet, lr As Long, lr2 As Long
Worksheets.Add
ActiveSheet.Name = "Compiled Master"
For Each sh In Worksheets
    If sh.Name <> "Compiled Master" Then
        sh.Activate
        lr2 = sh.Cells(Rows.Count, "B").End(xlUp).Row
        lr = Sheets("Compiled Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
        sh.UsedRange.AutoFilter Field:=2, Criteria1:="I"
        [color=red]Set rang = sh.Range("A4:H" & lr2).Offset(1, 0)[/color] 'change "H" to your last used column letter
        Set rang = rang.Resize(rang.Rows.Count - 1)
        Set rang = rang.SpecialCells(xlCellTypeVisible)
            rang.Copy Sheets("Compiled Master").Range("A" & lr)
        sh.Cells.AutoFilter
    End If
Next sh
End Sub

 

[chyno813]

thanks for help, but it show error on

Set rang = rang.SpecialCells(xlCellTypeVisible)

can you help to check

 

[Michael M]

Is one of the sheets blank ?
OR
When the filter is applied, there are no rows visible ?