Pull data from one sheet to another based on criteria

March 2013 Test.xlsm

Hello,

I would like to be able to pull data from one sheet to another. I have attached a basic data file that has a rota on one tab 'week 2' and a deployment sheet on 7 suceeding worksheets. I would like to pull across the names and shift times of people working in specific areas on that day.

In esscence, I would like a macro to look up and down 'column E' in 'week 2' and if it contains a given letter i.e. 'R' for restaurant, the data from that row in colums A, B and D is pulled into the 'M' worksheet in the cell range A33:C47. I have manually entered the data into the 'M' sheet for the example. I would like this to run on the press of a button, so not automatically updated.

Thanks in advance for any help you can provide.

Mike

Hi Jaslake,

This works brilliantly. If I was to create a rota for 5 weeks, with 5 sets of daily deployment, could the code be easily duplicated and edited?

Thanks

Mike

In effect, I'd need that set of sheets 5 times, so I could do 5 weeks worth of rota's at once.

So:

Week 1
1 M
1 T
1 W
1 Th
1 F
1 Sa
1 Su
Week 2
2 M
2 T
2 W
2 Th
2 F
2 Sa
2 Su

etc...Would this be a case of me duplicating the 2 modules and changing the sheet references?

There are also department codes:

CB, CON, BOH

Again, thank you for your help