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.

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

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.