The following two codes should do the task for you.
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Follow Up" Then
.AutoFilter 7, "Follow Up", , , 7
Sheet1.Range("A" & Rows.Count).End(3)(2).PasteSpecial xlValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
This code will filter Column G in all sheets (except the Follow Up sheet) for the criteria "Follow Up". It then transfers the relevant rows of data to the Follow Up sheet. Place the code in a standard module and assign it to a button.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("G:G")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "Complete" Then
When you type the word Complete in any cell in Column G of the Follow Up sheet then click away (or press enter or down arrow), the relevant row of data will be deleted from the Follow Up sheet. This code does not need a button and needs to be placed in the Follow Up sheet module. So, to implement this second code:-
- Right click on the Follow Up sheet tab.
- Select "View Code" from the menu that appears.
- In the big white field that then appears, paste the second code.
Following is a sample workbook that I have prepared for you so that you can see how the codes work:-
Click on the "RUN" button to see the data from the source sheets transferred to the Follow Up sheet.
When you have some data in the Follow Up sheet, overwrite "Follow Up" in any cell in Column G with "Complete" then click away (or press enter or down arrow). You'll note that the relevant row of data is deleted.
I hope that this helps.