VBA for combining multiple worksheet with complete criteria into one worksheet

[abhi.dube1]

Hi,

I am learning VBA in excel so that few task could be done faster. I have a spread sheet for my team to enter their weekly plan. The spread sheet is shared so that any one can use the spread sheet at the same time in their individual worksheet.

At the end of week I would like to know summary of how many tasks are completed by my team and what would be plan for next week. Please see below example for each worksheet.

Project Title	Due date	Weekly Task	Owner	Status
CRR - winder /shaft scenario	30/04/2017	Weekly Workshop for Bow-tie Analysis	AD	On Track
SAP User Efficiency project	30/06/2017	Compare different product and develop business case	AD	On Track
Reinduction	31/03/2017	2017 re-induction	AD	On Track
CV10 gearbox anti-rollback	18/03/2017	Options for pulley roll back stop	AD	On Track
NAC trial	17/03/2017	Functional Description of secondary crusher	AD	On Track
BRC Mechanical	24/03/2017	Monthly BRC meeting agenda	AD	On Track
DTI - CV13 notice	24/03/2017	Prepare feedback on CV013 notice	AD	On Track

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

In the spread sheet, I have similar 8 worksheet with different name.

I would like to make VBA code for two summary spread sheet

1. Copy all the tasks which are assigned as "completed" in status column (column E) and prepare summary for all nine worksheet.
2. copy all the tasks which are assigned as "on track" or "behind" in status Column (column E) and prepare summary for all nine worksheet.

Please help me for VBA code

Thanks,
Abhishek

 

[abhi.dube1]

Anyone please help me

 

[My Aswer Is This]

So you want two new sheets added to the workbook. Named Summar1 and Summart2.
These sheets would include all rows from all sheets that meet your criteria is this correct?

 

[abhi.dube1]

The summary sheet named "last week completion" and "Next week plan" is already their. I want all rows from 4 worksheet name "Abhishek", Anthony", "eric", "Tony" that meet criteria.

 

[My Aswer Is This]

In your original post you did not name any sheets.
You said:
and prepare summary for all nine worksheet.
That would tell me all you have is nine worksheets

You never mentioned summary sheets were already created

Now in your last post you say:

I want all rows from 4 worksheet name "Abhishek", Anthony", "eric", "Tony" that meet criteria

It's hard helping you when you change your request.
And are you sure you have a sheet named "eric" and not "Eric"

Exact details are needed.

 

[abhi.dube1]

Apologies for creating confusion. Please see below exact details.
I have following worksheet in my spread sheet.
- Abhishek
- Anthony
- Eric
- Tony
- List
- YTD completion
- Last week Completion
- Next week Plan
- Backlog Project.

Above are nine work sheets in my spread sheet, where team is using only 4 worksheet name "Abhishek", "Anthony", "Eric", "Tony".

The team worksheet has 5 columns where last column is for status.
1. I want to copy all the rows where status is "complete" into the worksheet of "last week completion"
2. Copy all the rows where status is "on track" or "behind" into the work sheet of "next week Plan"

The other worksheets such has "list" or "backlog" are just a record and I do not want to do VBA .

I hope this should clarify.