Hello all.
1. I have a workbook with about 30 worksheets, each for each member of staff. How each worksheet looks like is shown in the first image below. There is one other additional sheet in this case named ‘Summary’ and looks like the second image below.
2. So, what I want is someone opens the ‘Summary’ sheet, input a date in cell B3, and the VBA code would then go through the other 30 worksheets, and extract data corresponding to that date only into the ‘Summary’ sheet. There could be more than one entry for a particular date in the worksheets. Not all the data in each worksheet is required. The data required is:
Day: from worksheet cell D6 to Summary sheet cell B4
Name: from worksheet cell I3 to Summary sheet cell A6 downwards
Emp No: from worksheet cell I2 to Summary sheet cell B6 downwards
Grade Worked: from worksheet cell E6 to Summary sheet cell C6 downwards
HGW?: from worksheet cell F6 to Summary sheet cell D6 downwards
Start Time: from worksheet cell G6 to Summary sheet cell E6 downwards
Finish Time: from worksheet cell H6 to Summary sheet cell F6 downwards
Person Covered: from worksheet cell I6 to Summary sheet cell G6 downwards
Total Hours (in MR): from worksheet cell M6 to Summary sheet cell H6 downwards
Type of Variation: from worksheet cell S6 to Summary sheet cell I6 downwards
Authorised By: from worksheet cell Q6 to Summary sheet cell J6 downwards
3. (If possible, not essential, as could be done manually), once the data has been extracted, for the summary for that date be saved as a separate workbook in a specified directory/location.
Many thanks.
First image
Second image
1. I have a workbook with about 30 worksheets, each for each member of staff. How each worksheet looks like is shown in the first image below. There is one other additional sheet in this case named ‘Summary’ and looks like the second image below.
2. So, what I want is someone opens the ‘Summary’ sheet, input a date in cell B3, and the VBA code would then go through the other 30 worksheets, and extract data corresponding to that date only into the ‘Summary’ sheet. There could be more than one entry for a particular date in the worksheets. Not all the data in each worksheet is required. The data required is:
Day: from worksheet cell D6 to Summary sheet cell B4
Name: from worksheet cell I3 to Summary sheet cell A6 downwards
Emp No: from worksheet cell I2 to Summary sheet cell B6 downwards
Grade Worked: from worksheet cell E6 to Summary sheet cell C6 downwards
HGW?: from worksheet cell F6 to Summary sheet cell D6 downwards
Start Time: from worksheet cell G6 to Summary sheet cell E6 downwards
Finish Time: from worksheet cell H6 to Summary sheet cell F6 downwards
Person Covered: from worksheet cell I6 to Summary sheet cell G6 downwards
Total Hours (in MR): from worksheet cell M6 to Summary sheet cell H6 downwards
Type of Variation: from worksheet cell S6 to Summary sheet cell I6 downwards
Authorised By: from worksheet cell Q6 to Summary sheet cell J6 downwards
3. (If possible, not essential, as could be done manually), once the data has been extracted, for the summary for that date be saved as a separate workbook in a specified directory/location.
Many thanks.
First image
Second image