Untitled

Private Sub UserForm_Initialize()
    ' Populate the drop-down with country names on form load
    Dim ws As Worksheet
    Dim i As Long

    Set ws = ThisWorkbook.Sheets("Integrated Control sheet")

    Me.cmbOptions.Clear

    ' Populate ComboBox with country names (Columns E to U)
    For i = 5 To 21 ' Columns E:U
        If ws.Cells(2, i).Value <> "" Then
            Me.cmbOptions.AddItem ws.Cells(2, i).Value
        End If
    Next i
End Sub

Private Sub btnGenerateReport_Click()
    Dim wsMain As Worksheet
    Dim wsReport As Worksheet
    Dim lastRow As Long
    Dim selectedCountry As String
    Dim startCol As Long, endCol As Long
    Dim i As Long, col As Long
    Dim isRowEmpty As Boolean

    Set wsMain = ThisWorkbook.Sheets("Integrated Control sheet")
    Set wsReport = ThisWorkbook.Sheets("Report Sheet")
    wsReport.Cells.Clear ' Clear the Report Sheet

    ' Get the selected country from the ComboBox
    selectedCountry = Me.cmbOptions.Value
    If selectedCountry = "" Then
        MsgBox "Please select a country from the dropdown.", vbExclamation
        Exit Sub
    End If

    ' Find the country columns based on the selected country
    For col = 5 To 21 ' Columns E:U
        If wsMain.Cells(2, col).Value = selectedCountry Then
            startCol = col
            endCol = col + wsMain.Cells(2, col).MergeArea.Columns.Count - 1
            Exit For
        End If
    Next col

    If startCol = 0 Then
        MsgBox "Country not found in the headers!", vbExclamation
        Exit Sub
    End If

    ' Copy the headers (A:D) and the selected country's columns
    wsMain.Range("A1:D3").Copy Destination:=wsReport.Range("A1")
    wsMain.Range(wsMain.Cells(1, startCol), wsMain.Cells(3, endCol)).Copy _
        Destination:=wsReport.Cells(1, 5)

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

    ' Copy rows where the selected country's columns are not empty
    Dim reportRow As Long
    reportRow = 4 ' Start writing data from row 4 in the Report Sheet

    For i = 4 To lastRow
        isRowEmpty = True

        ' Check if any cell in the country's range has content
        For col = startCol To endCol
            If wsMain.Cells(i, col).Value <> "" Then
                isRowEmpty = False
                Exit For
            End If
        Next col

        ' Copy row if not empty
        If Not isRowEmpty Then
            wsMain.Range(wsMain.Cells(i, 1), wsMain.Cells(i, 4)).Copy _
                Destination:=wsReport.Cells(reportRow, 1)
            wsMain.Range(wsMain.Cells(i, startCol), wsMain.Cells(i, endCol)).Copy _
                Destination:=wsReport.Cells(reportRow, 5)
            reportRow = reportRow + 1
        End If
    Next i

    ' Inform the user
    MsgBox "Report generated successfully!", vbInformation
End Sub