Creating a report using VBA

[Sarah12345]

Hi All,

I would like to create a report page which allows to user to choose the columns they want to include in a report and then specify a start and end date, click submit and this copies the relevant data and pastes it into a report page. There are multiple worksheets so I need the code to start with the first one and then run through the rest.

This is the current logic that I am working with;
- Go to the first worksheet (Can't specify a specific worksheet name as the user might change this, or add more worksheets)
- Copy the name within cell "A1" to use as a report sub-header
- Finds all the columns on the worksheet which has 'Yes' above them (This row will always be in the same position)
- Finds all the column titles on the worksheet that meets the report column criteria, copy the headers
- Finds the specific rows within the date range that the user specified (The start and end dates will be on the report worksheet, these can be in a fixed cell location)
- For each row where the data range meets the date range and the report column criteria, copy the row.
- Return the report sub-header, paste it into the report worksheet
- Insert the rows below the sub-header
- Continue with next sheet

I've attached a few images of the raw data and attached an image of the type of report that I want to create.

Ultimately the aim is to allow the workshop team to be able to enter and record all of their data they need to and run a report to capture the info they need to plan their works for the next month etc.

My biggest hurdle so far is finding the data I need based on the report columns, most code i've seen requires me to provide a specific name to look for e.g. "service_date"...

Any help or guidance to get me a bit further on my way will be appreciated!

 

[Snakehips]

@Sarah12345
Is the column in each data sheet that holds the dates which are to be compared with the report date limits always headed 'Next Service Date' ?

 

[Snakehips]

@Sarah12345 If the answer to the above is yes then below may be a solution?

'Report' sheet start and end date cells must be fixed.
Green range E3:F4 hold the date filter criteria. NB E3 and F3 Must be the exact string of the header of the date columns.
Maybe you can position your command button to hide the green cells?
Tabs can be in any order but code currently assumes all tabs other than the 'Report' tab are data tabs to export from.

So Data 1

Book2
	A	B	C	D	E	F	G	H
1	Vehicle Maintenabce Tracker
2
3	Columns to include with reports
4
5	Yes	Yes	Yes	No	No	No	Yes	No
6
7	Type	Make	Model	Registration	Service Date	Mileage	Next Service Date	Next Service Mileage
8	Van	Citroen	Berling	N/A	5/6/21	12345	11/6/21	45372
9	Lorry	Mercedes	N/A	ABC 123	1/1/22	12345	7/23/22	45372
10	Van	Peugeot	Boxer	N/A	2/8/22	12345	8/8/22	45372
11	Van	Ford	Transit	N/A	12/20/21	12345	6/20/22	45372
12
Vehicles

Data 2

Book2
	A	B	C	D	E	F	G	H	I
1	Agitators Maintenance Tracker
2
3	Columns to include with reports
4
5	Yes	Yes	No	No	Yes	No	No	No	No
6
7	Type	Make	Serial No.	Sevice Date	Next Service Date	Engine	Oil Filter	1-FuelFilter	1-Fuel Filter
8	Agitator	CIFA	59927	1/1/22	7/1/22	Duetz	W 940/5	700 017	FF5018
9	Agitator	CIFA	61306	2/8/22	8/8/22	Duetz	W 940/5	700 017	FF5018
10	Agitator	CIFA	61306	12/20/21	6/20/22	Hatz	W 940/5	700 017	FF5018
11
Agitators

Data 3

Book2
	A	B	C	D	E	F	G
1	Other Stuff Maintenabce Tracker
2
3	Columns to include with reports
4
5	Yes	Yes	Yes	No	Yes	Yes	No
6
7	Stuff	More Stuff	Model	Service Date	Mileage	Next Service Date	Next Service Mileage
8	Test 1	qweqr	A Stuff	5/6/21	12345	2/2/22	45372
9	Test 2	abgeo	B Stuff	1/1/22	12345	7/13/22	45372
10	Test 3	qweqr	B Stuff	2/8/22	12345	8/8/22	45372
11	Test 4	tyurr	A Stuff	12/20/21	12345	8/8/22	45372
12
Stuff

Initial Report

Book2
	A	B	C	D	E	F	G
1	MaintenanceReport
2					*Green Range Holds Filter Criterea
3	Start Date	1/2/90			Next Service Date	Next Service Date
4					>=32875	<=44762
5	End Date	7/20/22
6
7	Stuff
8	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
9	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
10	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
11
12	Remnants of previous report
13	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
14	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
15	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
16
17	More flipping stuff
18	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
19	Loads of stuff	Loads of stuff	Loads of stuff	Loads of stuff
20
Report

Cell Formulas
Range	Formula
E4	E4	=">="&B3
F4	F4	="<="&B5

After code is run.

Book2
	A	B	C	D	E	F	G
1	MaintenanceReport
2					*Green Range Holds Filter Criterea
3	Start Date	1/2/90			Next Service Date	Next Service Date
4					>=32875	<=44762
5	End Date	7/20/22
6
7	Vehicle Maintenabce Tracker
8	Type	Make	Model	Next Service Date
9	Van	Citroen	Berling	11/6/21
10	Van	Ford	Transit	6/20/22
11
12	Agitators Maintenance Tracker
13	Type	Make	Next Service Date
14	Agitator	CIFA	7/1/22
15	Agitator	CIFA	6/20/22
16
17	Other Stuff Maintenabce Tracker
18	Stuff	More Stuff	Model	Mileage	Next Service Date
19	Test 1	qweqr	A Stuff	12345	2/2/22
20	Test 2	abgeo	B Stuff	12345	7/13/22
21
Report

Cell Formulas
Range	Formula
E4	E4	=">="&B3
F4	F4	="<="&B5

Code to be housed in the Report sheet code module.
Currently there is no coded provision for failing to find data in a sheet but that can be sorted.

Sub SarahTest()

Dim ReportRng As Range
Dim DataRng As Range
Dim CritereaRng As Range
Dim nxtr As Integer
Dim sht As Worksheet
Dim tws As Worksheet
Dim twb As Workbook

Set twb = ThisWorkbook
Set tws = twb.Sheets("Report")  'Edit if report sheet name differs
nxtr = tws.Range("A" & Rows.Count).End(xlUp).Row  'last row of report data
If nxtr < 8 Then nxtr = 10
tws.Range("A7:A" & nxtr).Rows.EntireRow.ClearContents  'clear existing report data
tws.Range("A7:A" & nxtr).Rows.EntireRow.ClearFormats  'Clear existing formats

For Each sht In twb.Sheets
    If sht.Name <> tws.Name Then    'process all sheets bar the report sheet
    
  nxtr = tws.Range("A" & Rows.Count).End(xlUp).Row + 2  'Next row for data title
  
  
    Set DataRng = sht.Range("A7").CurrentRegion   'data range of sheet
    Set ReportTL = tws.Range("A" & nxtr + 1) 'top left cell of destination range in report sheet
    Set CritereaRng = tws.Range("E3:F4")  'cells with date criterea for the filter
    
    ReportCol = 1  'initialise for column A
    tws.Range("A" & nxtr) = sht.Range("A1")  'data sheet title
    nxtr = nxtr + 1
    
    ' loop through row 5 of data to determine the 'Yes' columns
    For DataCol = 1 To DataRng.Columns.Count
      If sht.Cells(5, DataCol) = "Yes" Then
        ReportTL.Offset(0, ReportCol - 1) = sht.Cells(7, DataCol)  'if Yes add the data header to the report sheet
        ReportCol = ReportCol + 1  'increment for next report column
    End If
    Next DataCol
       
    Set ReportRng = tws.Range(Cells(nxtr, 1), Cells(nxtr, ReportCol - 1)) 'range of eport headers that will effect column sort
      
    DataRng.AdvancedFilter xlFilterCopy, CritereaRng, ReportRng
    End If
Next sht

End Sub

I hope that helps.