Results 1 to 4 of 4

Thread: Macro to copy rows from multiple worksheets to single summary sheet

  1. #1
    New Member
    Join Date
    Jul 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro to copy rows from multiple worksheets to single summary sheet

    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!

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,016
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro to copy rows from multiple worksheets to single summary sheet

    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
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    New Member
    Join Date
    Jul 2013
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to copy rows from multiple worksheets to single summary sheet

    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...

  4. #4
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,016
    Post Thanks / Like
    Mentioned
    84 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Macro to copy rows from multiple worksheets to single summary sheet

    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.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •