Extract data from multiple worksheets to master worksheet. Date as criteria.

[leelabird]

Greetings all,

I have inherited a workbook from a former employee which I am attempting to automate in order to save hours upon hours of manual copy/pasting.

The workbook has multiple worksheets with relevant departmental data used to assess employee performance. The worksheets are named by department (e.g. Dept.A, Dept.B, Dept.C..........and so on) and data from these worksheets is collated into a master summary worksheet named "Dept. Summary". The data in each worksheet has headers in row1 with actual data starting in row2. The columns used are from Column A to Column V. The amount of rows vary per month depending on the amount of data that is collected.

Each month, varying training/assessment programmes are undertaken by each employee with the department Supervisor filling in all the required data which is then sent to me after each training session. The day after each training session, I have to collate all the data to prepare a performance report for our Head Accountant. His requirement is that this needs to be done the day after each training session "without fail" (I'm afraid that he's a refugee from the 1950's! ). There are many departments with many employees and the manual method of copy/pasting used by previous employees is simply not productive. The time would be better spent on the many other daily duties.

I found the best approach would be to use the auto filter with yesterday's date as the criteria. The date criteria is in Column I. I don't want to use the employees names as the criteria as not all employees do the same training on the same day. I did find a suitable VBA macro code online which would do the copy/pasting from each department worksheet to the Dept. Summary worksheet but I am having trouble trying to get it to recognise the date (yesterday's date). I've tried using Date, CDate and CLong but to no avail. Would anyone be able to help a damsel in distress?

Thanking you all for any help/suggestions.

Kind regards,
Leela.

[vcoolio]

Hello Leela,

Try the following code placed in a standard module and assigned to a button:-

Please Login or Register  to view this content.

I've used the sheet code (Sheet1) instead of the sheet name for the destination worksheet.

I hope that this helps.

Cheerio,
vcoolio.

[leelabird]

Hi vcoolio,

Perfect! So simple! It works a treat. I've tested it a number of times to satisfy myself that all is well. No problems at all.
Hours and hours of work can now be done in an instant!
Could you please explain this line for me just so that I understand what is happening. I don't see any mention of dates!

.AutoFilter 9, 2, 11

I know the 9 represents Column I (the date column) but the rest? Could you also advise how I could just copy/paste values. There is formatting and formulae in the department worksheets which are not required in the summary worksheet.

Thank you so much!!

Kind regards,
Leela.

[vcoolio]

Hello Leela,

To copy/paste values only, amend the code as follows:-

Please Login or Register  to view this content.

The bits in red font are the amendments.

With this line of code:-

Please Login or Register  to view this content.

the autofilter filters on column I (9), 2 is the enumeration for xlFilterYesterday (so, in effect, there is mention of a date) and 11 is the enumeration for the operator xlFilterDynamic.

I hope that this clears things up a little for you.

Cheerio,
vcoolio.

[leelabird]

Hi vcoolio,

Thank you for doing that for me and thank you for explaining the enumerations. All this is much appreciated. You've made my day!

Kind regards,
Leela.

[vcoolio]

You're welcome Leela.

I'm glad to have been able to assist.

Cheerio,
vcoolio.