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

Excelme at work

New Member
Joined
Feb 7, 2018
Messages
18
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Not elegant but....add a sheet called "Summary" before running the code
VBA 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
 
Solution

Excelme at work

New Member
Joined
Feb 7, 2018
Messages
18
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
ABCDEFGHIJKLMNO
8Meeting reference / WorkstreamAction NumberTask Action / Decision detailMeeting details (If applicable)Date AssignedDue DateWorkstreamStatus CAIRAIDraft Instruction Y/NComments and Progress OwnerIndividual owner (if required)
9
10
11
12
13
14
15
16
17
18
19
20
21
Summary
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G8Cell Valuebetween 43101 and $F$2textNO


EEP Trisac Action Tracker Master LCV2.xlsm
ABCDEFGHIJKLMNO
8Meeting reference / WorkstreamAction NumberTask Action / Decision detailMeeting details (If applicable)Date AssignedDue DateWorkstreamStatus CAIRAIDraft Instruction Y/NComments and Progress OwnerIndividual owner (if required)
9Trisac1ActionTrisac mtg 1 TA re-group27/01/202102/02/2021Fit outOpen JC
10Trisac2Trisac mtg 2Public Realm02/02/2021Fit outOpen DB
11Trisac3Trisac mtg 3 Core Clinical Group Additional Meeting for S&C Review27/1/202101/03/2021Shell and CoreOpen KF
12Trisac4Trisac mtg 4Core Clinical Group Additional Meeting for S&C Review27/1/202108/03/2021FireOpen NE
13Trisac5Trisac Mtg 5Core Clinical Group Additional Meeting for S&C Review27/1/202101/04/2021SecurityOpen JC
14Trisac6Triac Mtg 6 Open
15Trisac7Trisac mtg 7 Open
EEP TriSac Mtg
Cells with Conditional Formatting
CellConditionCell FormatStop If True
N10:N13Expression=$I10="CLOSED"textNO
N9Expression=$I9="CLOSED"textNO
I10:I70Expression=$I10="CLOSED"textNO
H10:H70Expression=$I10="CLOSED"textNO
F10:F13Expression=$I10="CLOSED"textNO
E10Expression=$I10="CLOSED"textNO
B10:B14,B16:B23Expression=$I10="CLOSED"textNO
A10:A70Expression=$I10="CLOSED"textNO
D11Expression=$I11="CLOSED"textNO
C9Expression=$I9="CLOSED"textNO
G9Dates Occurringlast monthtextNO
G9Dates Occurringnext weektextNO
G9Dates Occurringthis weektextNO
G9Dates Occurringthis weektextNO
I9Expression=$I9="CLOSED"textNO
J9:M9,E9:H9,B9,B15,O9Expression=$I9="CLOSED"textNO
A9Expression=$I9="CLOSED"textNO
G14Dates Occurringlast monthtextNO
G14Dates Occurringnext weektextNO
G14Dates Occurringthis weektextNO
G13Dates Occurringlast monthtextNO
G13Dates Occurringnext weektextNO
G13Dates Occurringthis weektextNO
G12Dates Occurringlast monthtextNO
G12Dates Occurringnext weektextNO
G12Dates Occurringthis weektextNO
G10Dates Occurringlast monthtextNO
G10Dates Occurringnext weektextNO
G10Dates Occurringthis weektextNO
G11Dates Occurringlast monthtextNO
G11Dates Occurringnext weektextNO
G11Dates Occurringthis weektextNO
G10Dates Occurringthis weektextNO
G10:G14Expression=$I10="CLOSED"textNO
G12:G14,G8:G10Cell Valuebetween 43101 and $F$2textNO
Cells with Data Validation
CellAllowCriteria
H9:H70List='List data'!$E$3:$E$12
I9:I70List='List data'!$D$3:$D$4
C9List='List data'!$C$3:$C$4
A9:A15List=Meeting_reference
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Excelme at work

New Member
Joined
Feb 7, 2018
Messages
18
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
A dumb question, but......They are definitely column "I" aren't they ??
 

Excelme at work

New Member
Joined
Feb 7, 2018
Messages
18
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,168
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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 ??
 

Watch MrExcel Video

Forum statistics

Threads
1,130,015
Messages
5,639,558
Members
417,098
Latest member
steverob

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
Top