Particular value in every worksheet

[someone21]

I want to get a macro, not anything related with auto filter.

The issue is I want to get all the rows in all the worksheets except "main" which contain a cell value of "good" in the column C which is to be pasted into the "main" worksheet.Thereby, becoming a kind of like a summary of the "good" rows in other worksheets

 

[tigeravatar]

liverpooll21,

Not sure why you wouldn't want to use autofilter for something like this. Even the macro uses it:

Sub tgr()
    
    Static wsMain As Worksheet: Set wsMain = ActiveWorkbook.Sheets("main")
    
    Dim ws As Worksheet
    Dim rngDest As Range
    
    For Each ws In ActiveWorkbook.Sheets
        If ws.Name <> "main" Then
            Set rngDest = wsMain.Cells(Rows.Count, wsMain.UsedRange.Column).End(xlUp).Offset(1, 0)
            With Intersect(ws.UsedRange, ws.Columns("C"))
                .AutoFilter 1, "good"
                .Offset(1).EntireRow.Copy rngDest
                .AutoFilter
            End With
        End If
    Next ws
    
End Sub

Hope that helps,
~tigeravatar

 

[someone21]

Thanks, similar question,

now I want to get everyrow which contains any text into the main worksheet? how to do that?

 

[tigeravatar]

'Replace "good" with desired text to search for
'If you're simply searching for non-blank cells, replace "good" with "<>"
'This replacement is in the following line:
 
.AutoFilter 1, "good"