Copy rows based on cell value "Open" from multiple sheets

[Excelme at work]

I have a workbook with multiple tabs which each represent a different workstream / working group.
Each tab is effectively an action tracker based on each meeting group.

I would like a summary page to search for all items that are listed as "open" in Column i, and then copy the entire rows to the summary sheet.
I would need this function to search all the tabs for each workstream and then create 1 open action list.

Is this possible?

 

[Michael M]

Not elegant but....add a sheet called "Summary" before running the code

Sub MM2()
Dim sh As Worksheet, lr2 As Long, lr As Long, r As Long
lr2 = Sheets("Summary").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For Each sh In Worksheets
        If sh.Name <> "Summary" Then
             sh.Activate
             lr = Cells(Rows.Count, "I").End(xlUp).Row
                 For r = 2 To lr
                    If Cells(r, "I").Value = "Open" Then
                        Rows(r).Copy Sheets("Summary").Range("A" & lr2)
                    lr2 = lr2 + 1
                    End If
                Next r
        End If
    Next sh
End Sub

 

[Excelme at work]

Thanks, I still don't seem to be able to make that work though.
For ease all the individual workstream tabs and the summary tab are in the same layout.

EEP Trisac Action Tracker Master LCV2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Meeting reference / Workstream	Action Number	Task	Action / Decision detail	Meeting details (If applicable)	Date Assigned	Due Date	Workstream	Status	CAI	RAI	Draft Instruction Y/N	Comments and Progress	Owner	Individual owner (if required)
9
10
11
12
13
14
15
16
17
18
19
20
21
Summary

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G8	Cell Value	between 43101 and $F$2	text	NO

EEP Trisac Action Tracker Master LCV2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
8	Meeting reference / Workstream	Action Number	Task	Action / Decision detail	Meeting details (If applicable)	Date Assigned	Due Date	Workstream	Status	CAI	RAI	Draft Instruction Y/N	Comments and Progress	Owner	Individual owner (if required)
9	Trisac	1	Action	Trisac mtg 1	TA re-group	27/01/2021	02/02/2021	Fit out	Open					JC
10	Trisac	2		Trisac mtg 2	Public Realm		02/02/2021	Fit out	Open					DB
11	Trisac	3		Trisac mtg 3	Core Clinical Group Additional Meeting for S&C Review	27/1/2021	01/03/2021	Shell and Core	Open					KF
12	Trisac	4		Trisac mtg 4	Core Clinical Group Additional Meeting for S&C Review	27/1/2021	08/03/2021	Fire	Open					NE
13	Trisac	5		Trisac Mtg 5	Core Clinical Group Additional Meeting for S&C Review	27/1/2021	01/04/2021	Security	Open					JC
14	Trisac	6		Triac Mtg 6					Open
15	Trisac	7		Trisac mtg 7					Open
EEP TriSac Mtg

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N10:N13	Expression	=$I10="CLOSED"	text	NO
N9	Expression	=$I9="CLOSED"	text	NO
I10:I70	Expression	=$I10="CLOSED"	text	NO
H10:H70	Expression	=$I10="CLOSED"	text	NO
F10:F13	Expression	=$I10="CLOSED"	text	NO
E10	Expression	=$I10="CLOSED"	text	NO
B10:B14,B16:B23	Expression	=$I10="CLOSED"	text	NO
A10:A70	Expression	=$I10="CLOSED"	text	NO
D11	Expression	=$I11="CLOSED"	text	NO
C9	Expression	=$I9="CLOSED"	text	NO
G9	Dates Occurring	last month	text	NO
G9	Dates Occurring	next week	text	NO
G9	Dates Occurring	this week	text	NO
G9	Dates Occurring	this week	text	NO
I9	Expression	=$I9="CLOSED"	text	NO
J9:M9,E9:H9,B9,B15,O9	Expression	=$I9="CLOSED"	text	NO
A9	Expression	=$I9="CLOSED"	text	NO
G14	Dates Occurring	last month	text	NO
G14	Dates Occurring	next week	text	NO
G14	Dates Occurring	this week	text	NO
G13	Dates Occurring	last month	text	NO
G13	Dates Occurring	next week	text	NO
G13	Dates Occurring	this week	text	NO
G12	Dates Occurring	last month	text	NO
G12	Dates Occurring	next week	text	NO
G12	Dates Occurring	this week	text	NO
G10	Dates Occurring	last month	text	NO
G10	Dates Occurring	next week	text	NO
G10	Dates Occurring	this week	text	NO
G11	Dates Occurring	last month	text	NO
G11	Dates Occurring	next week	text	NO
G11	Dates Occurring	this week	text	NO
G10	Dates Occurring	this week	text	NO
G10:G14	Expression	=$I10="CLOSED"	text	NO
G12:G14,G8:G10	Cell Value	between 43101 and $F$2	text	NO

Cells with Data Validation
Cell	Allow	Criteria
H9:H70	List	='List data'!$E$3:$E$12
I9:I70	List	='List data'!$D$3:$D$4
C9	List	='List data'!$C$3:$C$4
A9:A15	List	=Meeting_reference

 

[Excelme at work]

Is there a way to do with formula instead of VBA?

 

[Michael M]

Didn't work....doesn't help much !
where did you put the code ?
How did you run the code ?

 

[Excelme at work]

It has been a long time since I used VBA, I used the editor and created a new macro, saved it as "summary sheet open" and then ran the Macro.
It took me to an empty row below the last open item on the last tab.

 

[Michael M]

Ok, where did you put the macro ?
Is the "Summary" sheet spelt with a capital "S"
Are the "Open" staus spelt with a capiral "O" ?
The code works fine for me?
Are the worksheets protected ?

 

[Michael M]

A dumb question, but......They are definitely column "I" aren't they ??

 

[Excelme at work]

Definitely column "i"
excel recently been updated so can only find developer tab using Alt f11
I then saved the macro and it shows as "in this workbook"
When i run I need the list to start at A9 on the summary sheet.
I did include a mini sheet if that helps?

Appreciate your help. I had hoped this could be done with a simple vlookup for "open" and then copy the entire row in order but it doesnt seem to play out that way

 

[Michael M]

Copied your "mini sheets" to my workbook and it worked fine!....with my data.
How does "Open" get inserted?, by dropdown box or manually ?
Have you checked the status to make sure there are no typos .....leading / trailling spaces, etc !
Can you upload the workbook to DropBox, or similar and then post a link to it back here ??