Report by location and date range

**rushdenx1** · 05-18-2011, 03:37 AM

The enclosed file is an extract from a much larger Workbook. Data is added on a daily basis to the Data sheet and can be any length. It shows performance data for many locations and by date. I would like to be able to produce quick reports on request for any location and date range. Under the 'Location Report Dep' tab I have shown how I would like the Report to look for an exampled location, together with planned, run and on time numbers and %'s. Can someone advise me as to the best way of producing these reports quickly and easily. I would like the Macro to be able to work in the larger Master Workbook (which contains other worksheets and macros) This is called 'PerformanceDataDump' and all the Data is contained on a worksheet called 'Data' (it is exactly the same layout as the extract I have posted) The report will still appear under a Worksheet called 'Location Report Dep'. Any help is appreciated. Kevin.

**Roger Govier** · 05-18-2011, 07:47 AM

Hi

The attached file should do what you want.

I have set up a new report on tab Report2.
This uses Advanced Filter to extract the rows based upon your selection criteria of Origin and Dates.

I had to amend the headings so that the headings in the source data match those in row 10 of Report2. Also, any blank entries in row 1 of data were filled with labels Blank, Blank1 etc. as Advanced filter requires headings for each column. I made the text White, so they don't show on your file.

I have used Freeze panes, to hide rows 1 to 3, where the criteria are held for advanced filter. The cells here pick up the values you enter in C5, C6 and E6 of Report2

I created some dynamic named ranges the details of which can be seen on the tab Named ranges.

The code which does the extraction is as follows

Please Login or Register  to view this content.

Note that if you rename the sheet, then you will need to alter the relevant line in the code.

I also added some event code on sheet Report2, which triggers the Extract as soon as you change the values in any of the criteria cells

Please Login or Register  to view this content.

I hope that I have interpreted correctly what you were saying you wanted as far as the calculations are concerned, but if not I am sure that you will be easily able to amend these.

I hope that this helps.