Data Summary Worksheet

[tjc154]

I have a workbook that contains 35 worksheets named as follows

WK1 DY1
WK1, DY2
WK1 DY3
WK1 DY4
WK1 DY5
WK2 DY1, etc all the way through week 7

I want to create a summary sheet that looks at data in columns
C=Computername
D= UserID
E=Username
F= Status.

If it finds any statuses that show Failed install in column F, it will export the data (columns C thru F) for a particular row into a summary worksheet called “status”. The status worksheet uses the same column names, so data would start populating on row A2.

How can I write this into a macro?

Thanks,

Tom

 

[tjc154]

To clarify, I would like the macro to look at all worksheets.

 

[Nalani]

How can I write this into a macro?

Have you attempt to write a macro yet? Or better yet tried to use the Macro Recorder?


Either way maybe this might help:

Sub TEST()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "SUMMARY" Then
            LR = Range("C" & Rows.Count).End(xlUp).Row
            ws.Range("C1:F" & LR).AutoFilter Field:=4, Criteria1:="FAILED"
            ws.Range("C2:F" & LR).SpecialCells(xlCellTypeVisible).Copy _
                    Destination:=Sheets("SUMMARY").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next
End Sub