Untitled

Private Sub UserForm_Initialize()
    ' Initialize the form and populate options
    Call PopulateOptions
End Sub

Private Sub optCountry_Click()
    ' Refresh options when "Country" is selected
    Call PopulateOptions
End Sub

Private Sub optStandard_Click()
    ' Refresh options when "Standard" is selected
    Call PopulateOptions
End Sub

Private Sub PopulateOptions()
    ' Populates the dropdown and list boxes for domains and priorities
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim uniqueDomains As Object, uniquePriorities As Object

    Set ws = ThisWorkbook.Sheets("Integrated Control sheet")
    Set uniqueDomains = CreateObject("Scripting.Dictionary")
    Set uniquePriorities = CreateObject("Scripting.Dictionary")

    ' Clear existing options
    Me.cmbOptions.Clear
    Me.lstDomain.Clear
    Me.lstRiskPriority.Clear

    ' Populate dropdown for "Country" or "Standard"
    If Me.optCountry.Value Then
        For i = 5 To 21 ' Columns E to U for Countries
            If ws.Cells(2, i).Value <> "" Then Me.cmbOptions.AddItem ws.Cells(2, i).Value
        Next i
    ElseIf Me.optStandard.Value Then
        For i = 22 To 29 ' Columns V to AC for Standards
            If ws.Cells(2, i).Value <> "" Then Me.cmbOptions.AddItem ws.Cells(2, i).Value
        Next i
    End If

    ' Populate unique domains (Column A)
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    For i = 4 To lastRow
        If ws.Cells(i, 1).Value <> "" Then uniqueDomains(ws.Cells(i, 1).Value) = True
    Next i

    For Each key In uniqueDomains.Keys
        Me.lstDomain.AddItem key
    Next key

    ' Populate unique risk priorities (Column AR)
    For i = 4 To lastRow
        If ws.Cells(i, 44).Value <> "" Then uniquePriorities(ws.Cells(i, 44).Value) = True
    Next i

    For Each key In uniquePriorities.Keys
        Me.lstRiskPriority.AddItem key
    Next key
End Sub

Private Sub btnGenerateReport_Click()
    Dim wsMain As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim selectedOption As String
    Dim selectedDomains As Object, selectedRiskPriorities As Object
    Dim i As Long, j As Long
    Dim reportRow As Long
    Dim startCol As Long, endCol As Long
    Dim headerRange As Range
    Dim optionFound As Boolean

    Set wsMain = ThisWorkbook.Sheets("Integrated Control sheet")
    Set wsReport = ThisWorkbook.Sheets("Report Sheet")

    ' Create dictionaries for selected filters
    Set selectedDomains = CreateObject("Scripting.Dictionary")
    Set selectedRiskPriorities = CreateObject("Scripting.Dictionary")

    wsReport.Cells.Clear ' Clear the Report Sheet

    ' Get selected combo box value (Country or Standard)
    selectedOption = Me.cmbOptions.Value

    If selectedOption = "" Then
        MsgBox "Please select an option from the dropdown.", vbExclamation
        Exit Sub
    End If

    ' Add selected domains to the dictionary
    For i = 0 To Me.lstDomain.ListCount - 1
        If Me.lstDomain.Selected(i) Then selectedDomains.Add Me.lstDomain.List(i), True
    Next i

    ' Add selected risk priorities to the dictionary
    For i = 0 To Me.lstRiskPriority.ListCount - 1
        If Me.lstRiskPriority.Selected(i) Then selectedRiskPriorities.Add Me.lstRiskPriority.List(i), True
    Next i

    optionFound = False

    If Me.optCountry.Value Then
        ' Handle Country-specific logic
        Set headerRange = wsMain.Rows(2).Find(What:=selectedOption, LookIn:=xlValues, LookAt:=xlWhole)
        If headerRange Is Nothing Then
            MsgBox "Country not found!", vbExclamation
            Exit Sub
        End If
        startCol = headerRange.Column
        endCol = headerRange.MergeArea.Columns.Count + startCol - 1
        optionFound = True
    ElseIf Me.optStandard.Value Then
        ' Handle Standard-specific logic
        For j = 22 To 29 ' Columns V to AC
            If wsMain.Cells(2, j).Value = selectedOption Then
                startCol = j
                endCol = j
                optionFound = True
                Exit For
            End If
        Next j

        If Not optionFound Then
            MsgBox "Standard not found in the headers!", vbExclamation
            Exit Sub
        End If
    End If

    ' Copy general headers (A:D)
    wsMain.Range("A1:D3").Copy Destination:=wsReport.Range("A1")

    ' Copy selected Country or Standard-specific headers
    wsMain.Range(wsMain.Cells(1, startCol), wsMain.Cells(3, endCol)).Copy _
        Destination:=wsReport.Cells(1, 5)

    ' Copy additional headers (AD:BB)
    wsMain.Range(wsMain.Cells(1, 30), wsMain.Cells(3, 54)).Copy _
        Destination:=wsReport.Cells(1, 5 + (endCol - startCol + 1))

    ' Generate the report based on selected filters
    lastRow = wsMain.Cells(wsMain.Rows.Count, 1).End(xlUp).Row
    reportRow = 4 ' Start writing data from row 4 in the Report Sheet

    For i = 4 To lastRow
        ' Check if the row matches the selected filters
        If (selectedDomains.exists(wsMain.Cells(i, 1).Value) Or selectedDomains.Count = 0) And _
           (selectedRiskPriorities.exists(wsMain.Cells(i, 44).Value) Or selectedRiskPriorities.Count = 0) Then

            ' Copy general data (A:D)
            wsMain.Range(wsMain.Cells(i, 1), wsMain.Cells(i, 4)).Copy _
                Destination:=wsReport.Cells(reportRow, 1)

            ' Copy data for selected Country or Standard
            wsMain.Range(wsMain.Cells(i, startCol), wsMain.Cells(i, endCol)).Copy _
                Destination:=wsReport.Cells(reportRow, 5)

            ' Copy additional data (AD:BB)
            wsMain.Range(wsMain.Cells(i, 30), wsMain.Cells(i, 54)).Copy _
                Destination:=wsReport.Cells(reportRow, 5 + (endCol - startCol + 1))

            reportRow = reportRow + 1
        End If
    Next i

    ' Perform deletion of rows with all blank cells in the country/standard columns
    Call RemoveBlankCountryRows(wsReport, startCol, endCol)

    MsgBox "Report generated successfully!", vbInformation
End Sub

Private Sub RemoveBlankCountryRows(wsReport As Worksheet, startCol As Long, endCol As Long)
    Dim lastRow As Long, i As Long
    Dim isRowEmpty As Boolean

    lastRow = wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row

    For i = lastRow To 4 Step -1
        isRowEmpty = WorksheetFunction.CountA(wsReport.Range(wsReport.Cells(i, startCol), wsReport.Cells(i, endCol))) = 0

        If isRowEmpty Then
            wsReport.Rows(i).Delete
        End If
    Next i
End Sub