Generate report based on list of data Validation.

Hello,

If we select the below list and hit the generate the report on other sheet. it will extract the data based on the below list.


Signed Provider
Not Signed Provider
CCHI Expired

Example: If i select signed provider it will extract all the signed provider based on column AS & AU.

Thanks in advance
Best Regards.

How do we segregate which rows belong to which group in the list? Is there some field?

Will have something ready for you in the morning.

Sorry, i was travelling so couldnt work out a solution. Will do something shortly.

Just to clarify- for SIgned it would be the entries in column AH, for expired, AV column = expired. What about unsigned? Would it be AH column where value is 0?

Right click on your 2nd sheet - Report selection and select "View Code". Copy the below code there.

Option Explicit
Dim lrow As Long
Dim i As Long

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

lrow = Worksheets("Database").Range("A" & Rows.Count).End(xlUp).Row

Select Case Worksheets("Report Selection").Range("B7").Value

Case "Signed Provider"
    Call Signed_P
Case "Not Signed Provider"
    Call Unsigned_P
Case "CCHI Expired"
    Call Expired
End Select

Worksheets("Report Result").Cells.EntireColumn.AutoFit

Worksheets("Report Result").Range("A2").Value = 1

Application.ScreenUpdating = True

End Sub

Sub Signed_P()
    Worksheets("Report Result").Cells.Delete
    Worksheets("Database").Rows("1:1").Copy Worksheets("Report Result").Range("A1")
    For i = 2 To lrow
        If Worksheets("Database").Range("AH" & i).Value = "1" Then
            Worksheets("Database").Rows(i & ":" & i).Copy Worksheets("Report Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
End Sub

Sub Unsigned_P()
    Worksheets("Report Result").Cells.Delete
    Worksheets("Database").Rows("1:1").Copy Worksheets("Report Result").Range("A1")
    For i = 2 To lrow
        If Worksheets("Database").Range("AH" & i).Value = "2" Then
            Worksheets("Database").Rows(i & ":" & i).Copy Worksheets("Report Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
End Sub

Sub Expired()
    Worksheets("Report Result").Cells.Delete
    Worksheets("Database").Rows("1:1").Copy Worksheets("Report Result").Range("A1")
    For i = 2 To lrow
        If Worksheets("Database").Range("AV" & i).Value = "Expired" Then
            Worksheets("Database").Rows(i & ":" & i).Copy Worksheets("Report Result").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        End If
    Next i
End Sub

Once you select the value in the dropdown and click on the button, your data will be refreshed in "Report Result".