brianfosterblack
Active Member
- Joined
- Nov 1, 2011
- Messages
- 251
I need a macro to run a report. My Workbook has about 30 client sheets (Number of sheets is variable) on which I record each client's details and in columns Z to AB from row 4 downwards are details of the date and time I have performed a task for this client and the task performed. Column Z is the date (dd/mm/yyyy) Column AA is the time ([HH]:mm) and column AB is the details of the work done. This list is in date order from 1st January to the end December and multiple task could be done for the same client or other clients on the same day. I currently use the following formula on each sheet to sum the hours spent with a client between 2 dates
I am finding it inconvenient to go to each sheet in the workbook to run this formula and look up work done between dates.
What I would like to do is summarize this on a Reports Sheet and I need a macro for this. This macro needs to run across all the worksheets in the workbook except sheets named Master, Invoices and Reports. On the Reports sheet in cell L1 is the start date (dd/mm/yyyy) and Cell L 2 is the end date (dd/mm/yyyy) both dates to be inclusive.
Starting Cell Reports!B4 I need to insert from each sheet the Sheet name the data is drawn from (All sheet names are 1 word) then the date the task was performed on (Column C) then the time spent on the task (Column D) and lastly the task performed (Column E). the Report shown on the Reports sheet will just list every individual task performed between the dates along with the client date and duration of time.
Once I have this on the Report Sheet I can then insert the above formula (with ranges changed) into L3 on the Report sheet and total the hours spent on all clients between the selected dates but I can then also see the actual work done for all clients.
Can anyone help with this macro to run across multiple sheets.
Excel Formula:
=SUMIFS(AA4:AA2000,Z4:Z2000,">="&AD1,Z4:Z2000,"<="&AD2)What I would like to do is summarize this on a Reports Sheet and I need a macro for this. This macro needs to run across all the worksheets in the workbook except sheets named Master, Invoices and Reports. On the Reports sheet in cell L1 is the start date (dd/mm/yyyy) and Cell L 2 is the end date (dd/mm/yyyy) both dates to be inclusive.
Starting Cell Reports!B4 I need to insert from each sheet the Sheet name the data is drawn from (All sheet names are 1 word) then the date the task was performed on (Column C) then the time spent on the task (Column D) and lastly the task performed (Column E). the Report shown on the Reports sheet will just list every individual task performed between the dates along with the client date and duration of time.
Once I have this on the Report Sheet I can then insert the above formula (with ranges changed) into L3 on the Report sheet and total the hours spent on all clients between the selected dates but I can then also see the actual work done for all clients.
Can anyone help with this macro to run across multiple sheets.
