My team uses an excel to track our projects. We have many tabs (8) , but on each tab on Column A, we have a drop down menu that we can select if it's "High Priority" or "Low Priority".
Is there a way we can make a new excel sheet/word document to generate all these high/low priority items on one single tab or work document, so it would be easier to just see in one viewing instead of clicking all these tabs.
The Columns go like "Status" (A) , "Assigned To" (B) , "Task" (C) , Comments(D)
The High Priority/Low Priority is under column A, and we would like to have the whole row to be copied A:D when it says High Priority or Low Priority into that new excel sheet or document.
1) My Excel actually has 10 tabs - a tab represents the project. The tab list will keep growing or it may lessen depending on what projects are given or are completed.
2) I want one result sheet for both high and low priority, so we can just look at it on one sheet and print it out.
*** on A1 of each excel sheet - I have a heading with the project name - I want to transfer that in the High/Low Priority sheet so my department knows what project that task is assigned to
For Each ws In Worksheets
If ws.Name = "Overview" Then GoTo Skip
ws.Activate
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A3:A" & lRow)
If cell = "High Priority" Or cell = "Low Priority" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Range("A1").Copy Sheets("Overview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next cell
Skip:
Next ws
End Sub
This is exactly what I put after I made an Overview sheet.
So all your sheets are projects with the project name in A1 and header in row 2.
For the code it doesn't matter how many sheets you have, so you can add as many as you want.
I named the result sheet Overview. If you want a different name then don't forget to alter the code (best done by Ctrl+H; find and replace).
Before running the code create the Overview sheet.
On the Overview the project names are placed in column E and header in row 1.
When the code is run:
- The Overview sheet is cleared, except for it's header in row 1.
- All sheets except the Overview sheet are scanned for "High Priority" and "Low Priority" in column A. When found placed in the Overview sheet.
Here is the code:
Sub RunMe()
Dim ws As Worksheet
Dim lRow As Long
Sheets("Overview").Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents
For Each ws In Worksheets
If ws.Name = "Overview" Then GoTo Skip
ws.Activate
lRow = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A3:A" & lRow)
If cell = "High Priority" Or cell = "Low Priority" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "D")).Copy
Sheets("Overview").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial
Range("A1").Copy Sheets("Overview").Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next cell
Skip:
Next ws
End Sub
- Posts
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396- Posts
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396Careful with personal information.
- Posts
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396- Posts
- 2591
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- February 11, 2020
396Change (4th code line):
Sheets("Overview").Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents
into:
Sheets("Overview").Select
Range(Cells(2, "A"), Cells(Rows.Count, "E")).ClearContents
- Posts
- 5
- Registration date
- Friday July 25, 2014
- Status
- Member
- Last seen
- August 5, 2014
1