VBA Code needed to match a date and then copy a range and paste values

[ettamucci]

Hi,
I am useless with VBA and was wondering if someone could assist with the following:

I have created a rudimentary Fortnightly Staff Roster, with a corresponding time sheet page for each of my 30 staff members.

1. At the end of each work week I select 'Yes' (drop down list) in column B of each sheet for that week;
2. This then searches the ROSTER sheet, return's the staff member's rostered hours for each day of that week, and calculates total hours worked, gross pay, tax etc.
3. After this i have to copy the cells from columns C to AO for that week-ending row and Paste Special>Values. This is needed because each fortnight the Roster sheet is changed to reflect the upcoming fortnight.

I have a very simple macro that will do the copy and paste but i currently have to select the cell (where the macro should start) on each and every of the 30 worksheets.

I was wondering if there is a way to have a macro that would do the following:

1. Find the matching date row in each sheet (based on sheet Roster!G23);
2. Copy the corresponding range from that row (columns C:AO);
3. and Paste Special > Values onto the same range.

for each of the 30 staff worksheets.

I have attached a sample of the file.

thanks in advance.

[Trebor76]

Hi ettamucci (and fellow Sydneyite ),

Try this:

Please Login or Register  to view this content.

Regards,

Robert

[ettamucci]

Hi Robert,
Absolute LEGEND!!!

Could I please be a pain, but I forgot to mention it previously. I also needed it to change the corresponding value in Column B from 'No' to 'Yes' so that it can populate the row with data before the copy and paste.

i am not sure if this would work though or whether it would have to be a separate macro that is run first.

thank you again.

[Trebor76]

Could I please be a pain, but I forgot to mention it previously. I also needed it to change the corresponding value in Column B from 'No' to 'Yes' so that it can populate the row with data before the copy and paste.

No problem - try this:

Please Login or Register  to view this content.

Note I've also included code to exclude the Data tab.

Regards,

Robert

[ettamucci]

Thanks again Robert.

Love your work!!

[Trebor76]

You're welcome. Thanks for marking the thread as solved and for the rep