Consolidating data from two sheets, and hiding specific rows

[kspab]

[FONT=&quot]I have two sheets for my engineers that have their active projects listed. I have a third sheet for all projects that have been completed. I'm looking for a way to pull all of my projects from sheet 1 and 2, and lump them together in sheet 3. Then, if I could have a column to specify if the project has been completed, it hides from sheet 1 & 2 and is shown on sheet 3 (Completed sheet).[/FONT]

 

[mumps]

OK. You won't need the ID column then. We can use the project name as the unique ID. I'll get back to you with something you can try as soon as I can.

 

[mumps]

Place this macro in a standard module. When you run this macro, it will clear all data from the "Completed" sheet and copy the data from Sheet 1 and 2.

Sub CombineSheets()
    Application.ScreenUpdating = False
    Sheets("Completed").Cells.ClearContents
    Sheets("Sheet1").UsedRange.Copy Sheets("Completed").Cells(1, 1)
    Sheets("Sheet2").UsedRange.Offset(1, 0).Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1, 0)
    Application.ScreenUpdating = True
End Sub

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your "Completed" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Completed" in any cell in column D and exit the cell. That project row in either Sheet1 or Sheet2 will be hidden.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Dim projname As Range
    If Target = "Completed" Then
        Set projname = Sheets("Sheet1").Range("A:A").Find(Cells(Target.Row, 1), LookIn:=xlValues, lookat:=xlWhole)
        If Not projname Is Nothing Then
            Sheets("Sheet1").Rows(projname.Row).Hidden = True
        Else
            Set projname = Sheets("Sheet2").Range("A:A").Find(Cells(Target.Row, 1), LookIn:=xlValues, lookat:=xlWhole)
            If Not projname Is Nothing Then
                Sheets("Sheet2").Rows(projname.Row).Hidden = True
            End If
        End If
    End If
    Application.ScreenUpdating = True
End Sub

 

[kspab]

This is great, thank you so much.

I do have a question though. is there a simpler way of doing this through filters? This works for my purpose and I'll use it if there isn't another way, but running the macro every so often isn't ideal. Is there a simple method to aggregate all my data from the two sheets into one and use column filters to just filter out the non-completed projects on the completed sheet?

 

[mumps]

You could automate the transfer of data from the 2 sheets to the "Completed" sheet as each project is entered in each sheet. The user could insert "Completed" in the 'Status' column when the project has been completed and that row would be atomically transferred to the "Completed" sheet. That row could then be left as is, be hidden or deleted from the sheet if that is what you want. Would that work for you? As far as filtering is concerned, do you want to do that manually or would you want a macro to do that?

 

[kspab]

You could automate the transfer of data from the 2 sheets to the "Completed" sheet as each project is entered in each sheet. The user could insert "Completed" in the 'Status' column when the project has been completed and that row would be atomically transferred to the "Completed" sheet. That row could then be left as is, be hidden or deleted from the sheet if that is what you want. Would that work for you? As far as filtering is concerned, do you want to do that manually or would you want a macro to do that?

Yes, this is exactly what I'm looking to do, and in it's simplest form. I don't want the two engineers working on this sheet to have to use any special plugins or run a macro every time they need to update. I'm simply looking for a way, that once a project on either sheet 1 or 2 is marked 'Complete' it appears on sheet 3. It would be preferable for this to all be automated. Is this possible?

 

[mumps]

Place this macro in the code modules for Sheet1 and Sheet2 as I described in Post #12 . All the engineers have to do is enter the word "Completed" in column D (Status), exit the cell and that project will be transferred to the "Completed" sheet. To avoid typo's and make it easier when the engineers enter the word "Completed", I would suggest that you insert a drop down using a data validation list composed of just one option (Completed) in each sheet in column D. Then all they have to do is click on a cell and select "Completed". This would make the process fully automated.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("D:D")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    If Target = "Completed" Then
        Target.EntireRow.Copy Sheets("Completed").Cells(Sheets("Completed").Rows.Count, "A").End(xlUp).Offset(1, 0)
    End If
    Application.ScreenUpdating = True
End Sub