Transferring Selected Data from Multiple Worksheets to OneSolved/Closed

Question

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

Need Help! Thanks.

TrowaD · Answer

Hi Cpogata,

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


Best regards,
Trowa

cpogata · Answer

I keep getting this error "Application Defined or Object Defined Error"

On the code line of Sheets("Overview").Range(Cells......etc.

Transferring Selected Data from Multiple Worksheets to One

2 replies

Subscribe To Our Newsletter!