VBA - Autofill table based on multiple cell entries

[MikeLangley]

I hope that someone can assist me with some VBA code to cover a little problem I am having?

I work for a company that engineers visiting sites on a daily basis to conduct air tests, sound tests, vent tests etc. and we are looking to map their jobs cumulatively month by month.

Importing the data from our CRM is covered, as is the geocoding, however I am having difficulty when trying to create a method to view the data more easily. I am trying to display the records in a table based on the particular service and month that it was carried out.

So I have 2 sheets, 1 for data containing the full cumulative list of jobs, and the 2nd for viewing a snapshot. The data page has the unique job reference, the name of the engineer, the name of the service being carried out and the site location.

The 2nd sheet contains two dropdowns and the table headings below. the dropdowns control the service and date. What I need is for the table to fill based on the entries in those 2 cells.


Can anyone help?

Mike.

 

[DanteAmor]

You could upload a copy of your file with some examples, to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

 

[MikeLangley]

Link to sample workbook:

https://drive.google.com/open?id=1eV5vc5V3IOkPXuot5QpEYI_zQrwxdVih

 

[DanteAmor]

In the viewer sheet, you could put the data in the following way with formulas too.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Show	Commercial Air Testing		From	February			Service	Date	Date
2								Commercial Air Testing	>=43497
3	Job Ref	Site Postcode	Service	Engineer	No. of Tests	Date			01/02/2019	28/02/2019
4	JB-011683	IP12 4LH	Commercial Air Testing	Keith Markham	1	22/02/2019
5	JB-011683	IP12 4LH	Commercial Air Testing	Keith Markham	1	22/02/2019
Viewer

I'm applying an advanced filter, that way the queries are faster.


Put the following macro in the events of your Viewer sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sh1 As Worksheet, sh2 As Worksheet, j As Long
    
    Set sh1 = Sheets("Data")
    Set sh2 = Sheets("Viewer")
    If Not Intersect(Target, Range("B1,E1")) Is Nothing Then
        If Target.Count > 1 Then Exit Sub
        If Range("E1").Value <> "" Then
            For j = 1 To 12
                If LCase(Format(DateSerial(Year(Date), j, 1), "mmmm")) = LCase(Range("E1").Value) Then
                    sh2.Range("I3") = DateSerial(Year(Date), j, 1)
                    sh2.Range("J3") = DateSerial(Year(Date), j, Day(DateSerial(Year(Date), j + 1, 1) - 1))
                End If
            Next
         End If
        sh1.Range("A1:F6").CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=sh2.Range("H1:J2"), CopyToRange:=sh2.Range("A3:F3"), Unique:=False
    End If
End Sub

(Right click the tab of the sheet you want this to work, select view code & paste the code into the window that opens up.)


test your file:
https://www.dropbox.com/s/80k9ohckvffsc6n/Sample.xlsm?dl=0