Consolidation of data based on search criteria

[pradeepkodali]

Hello,

I need your help in getting the data consolidated from multiple sheets to one sheet based on the week we select.

For Example: I have sheets named Project A, B & C (Variable) and each has 4 weeks (Variable) data. I am looking for a Macro which should ask for the "week" and suppose if I select Week 1 then it should copy the data from all the sheets of Week 1 to consolidated sheet as shown in the sample sheet.

Thanks in advance for your help!!

Regards,
PK

[AB33]

Enter a number (Not text )ONLY when asked.

[pradeepkodali]

Thanks AB33, its works like a Charm in the sample but not on my actual data sheet. I have attached my data sheet, can you please have a look.

Also is it possible to check the name in B2, C2,D2, E2,.... and check for the same Sheet names and copy the data?

Data will have around 15 to 20 sheets & 52 weeks Data.

Thanks much for your help!!

Regards,
PK

[AB33]

PK,
Merged cells and VBA are not good friends. I have hard time amending the code. The code was unable to find any week, so I have to remove all the data in row 2 and retype them again.

You should have consistent data layout. The weeks are in row 2.

[pradeepkodali]

Thanks a LOT for all your help AB33. Sorry for the trouble.