Macro to copy rows from multiple worksheets to single summary sheet

kyndylan

New Member
Joined
Jul 8, 2013
Messages
5
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!
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
Code:
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

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
Back
Top