Data Summary Worksheet

tjc154

Active Member
Joined
Apr 7, 2007
Messages
363
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
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:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,193
Messages
5,594,767
Members
413,933
Latest member
Msar5586

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top