Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

Transferring Selected Data from Multiple Worksheets to One [Solved/Closed]

Report
Posts
5
Registration date
Friday July 25, 2014
Status
Member
Last seen
August 5, 2014
 -
Posts
5
Registration date
Friday July 25, 2014
Status
Member
Last seen
August 5, 2014
 -
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.

2 replies

Answer 1 / 2
Posts
5
Registration date
Friday July 25, 2014
Status
Member
Last seen
August 5, 2014
 1
I keep getting this error "Application Defined or Object Defined Error"

On the code line of Sheets("Overview").Range(Cells......etc.
1
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

View all 8 comments
Report
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
Depends on how you did it. Could you post the code as it is now?
Report
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
Don't see anything wrong. Could you upload your file using a file sharing site like www.speedyshare.com or ge.tt and post back the download link.

Careful with personal information.
Report
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
Do you have your worksheet/book protected?
Report
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
I think I got it.

Change (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
Report
Posts
5
Registration date
Friday July 25, 2014
Status
Member
Last seen
August 5, 2014
 1
It works! THANK U !
Answer 2 / 2
Posts
2718
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 15, 2021
 459
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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!