Code for searching multiple worksheets and based on criteria copy and paste that data

Rather than simply creating what's essentially a work around which wouldn't be necessary if you had a proper single sheet database, are you up to creating that database first. Analysing anything thereafter would be trivial. A simple macro could combine all 52 sheets into one large database which would allow you to get rid of the 52 sheets.

When you need to see details for a particular week/month/quarter/other criteria such as the one you describe here you would use an advanced data filter based on the database to output that subset of records.

Mmm, bosses, what do they really know about these things.

OK, how about a half way house
1. Retain your 52 sheets for the moment
2. Create a single sheet containing all the data from the 52 sheets
3. Create a template extract sheet for extracting anything your boss is interested in - i.e. yor specific request
4. Convince your boss that it really isn't sensible and is not efficient - this is a case in point since at the moment you can't achieve the task s/he wants
Point out to your boss that his/her insistence on having 52 weeks has added to the complexity and is more time consuming
5. Put in for a pay rise on the basis of you are now able to be even more productive.

Assuming the format of all 52 sheets are the same . i.e same columns, upload your workbook and we can no doubt quickly create a macro to get all the data on one sheet.