Untitled

Private Sub btnGenerateReport_Click()

    Dim wsMain As Worksheet
    Dim wsReport As Worksheet
    Dim wbNew As Workbook
    Dim lastRow As Long
    Dim selectedOption As String
    Dim selectedDomains As Object
    Dim 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
    Dim newFileName As String

    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
        End If
    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
        End If
    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 Or wsMain.Cells(3, 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(3, 54)).Copy _
                Destination:=wsReport.Cells(reportRow, 5 + (endCol - startCol + 1))

            reportRow = reportRow + 1
        End If
    Next i

    ' Now, call the function to remove rows with all blank country columns
    Call FilterAndRemoveBlankRows(wsReport)

    ' Create a new workbook and copy the report
    Set wbNew = Application.Workbooks.Add
    wsReport.Copy Before:=wbNew.Sheets(1)

    ' Name the new worksheet appropriately
    wbNew.Sheets(1).Name = "Generated Report"

    ' Prompt user for file name
    newFileName = Application.GetSaveAsFilename(InitialFileName:="Generated_Report.xlsx", FileFilter:="Excel Files (*.xlsx), *.xlsx")
    If newFileName <> "False" Then
        wbNew.SaveAs newFileName, FileFormat:=xlOpenXMLWorkbook
        MsgBox "Report generated and saved successfully!", vbInformation
    Else
        MsgBox "Report generation canceled.", vbExclamation
    End If

    wbNew.Close SaveChanges:=False ' Close the new workbook if not saved
    Unload Me

End Sub

Private Sub FilterAndRemoveBlankRows(wsReport As Worksheet)
    Dim lastRow As Long
    Dim i As Long
    Dim countryColumns As Variant
    Dim col As Variant
    Dim isRowEmpty As Boolean
    Dim countryColumnRange As Range

    ' Define the columns for each country (country column ranges)
    countryColumns = Array( _
        Array(5, 6, 7, 8),  ' UAE (E:H)
        , Array(9, 9)        ' Hong Kong (I:I)
        , Array(10, 11)      ' India (J:K)
        , Array(12, 12)      ' Kuwait (L:L)
        , Array(13, 13)      ' Qatar (M:M)
        , Array(14, 15)      ' Oman (N:O)
        , Array(16, 17)      ' Bahrain (P:Q)
        , Array(18, 19)      ' Pakistan (R:S)
        , Array(20, 21)      ' USA (T:U)
    )

    ' Find the last row in the report sheet
    lastRow = wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row

    ' Loop through rows from the bottom to the top
    For i = lastRow To 4 Step -1 ' Data starts from row 4
        isRowEmpty = True ' Assume the row is empty unless we find data

        ' Loop through the columns of each country (2-column pair or single column)
        For Each col In countryColumns
            ' Check the range for the current country and if it has any non-blank cell
            Set countryColumnRange = wsReport.Range(wsReport.Cells(i, col(0)), wsReport.Cells(i, col(UBound(col))))
            If WorksheetFunction.CountA(countryColumnRange) > 0 Then
                isRowEmpty = False ' At least one cell has data
                Exit For
            End If
        Next col

        ' If all the columns for this country are blank, delete the row
        If isRowEmpty Then
            wsReport.Rows(i).Delete
        End If
    Next i
End Sub