Macro to copy rows from multiple worksheets to single summary sheet

[kyndylan]

Hello, I hope you can help - my VBA skills are extremely lacking, but I don't think what I'm trying to achieve is actually too difficult.

I'm trying to copy rows from multiple sheets to one summary sheet based on whether the row shows 'completed' in a certain cell or not.

I have a workbook which contains eight worksheets. Worksheet 1 is a 'summary' sheet, and each of the other seven represents a project for my team.

Each project sheet has a single header row, and every row below that represents a single milestone for a project. Column K shows whether the milestone is complete or not, using the word 'completed'. Milestones which have not been completed have a blank cell in column K.

Can someone help me with a macro which runs through each sheet, finds all rows with a blank in cell K, and copies them to the summary sheet, one below the other? Ideally it would run every time you open the workbook.

One additional note - as more projects come on line, the number of sheets might grow - can this be taken into account?

Thanks in advance!

 

[mumps]

Place this macro in the code module for ThisWorkbook. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the left hand pane, double click on "ThisWorkbook". Copy/paste the macro into the empty window that opens up. Close the window to return to your sheet. Make sure you have a sheet named "Summary". Save the workbook as a macro-enabled file. The macro will run every time you open the workbook.

Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, desWS As Worksheet
    Set desWS = Sheets("Summary")
    desWS.UsedRange.ClearContents
    For Each ws In Sheets
        If ws.Name <> "Summary" Then
            With ws.Cells(1, 1).CurrentRegion
                .AutoFilter 11, "="
                ws.AutoFilter.Range.Offset(1, 0).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
                .AutoFilter
            End With
        End If
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[kyndylan]

Thanks mumps!

That seems to be 90% of the way there, but there are a couple of things which aren't quite right.

When I open the workbook I get an error message saying 'run time error 1004: AutoFilter method of Range class failed'.

Despite that error message, the macro appears to work, but only for the first sheet - it doesn't copy rows from the other six.

Thanks again...

 

[mumps]

I tested the macro on some dummy sheets and it worked properly. Is the data in the second sheet which wasn't copied set up exactly the same way as the data in the copied sheet? I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.