Add item to combobox from multiple range with criteria from multiple sheets

Question

I have a combobox on a userform that i need to add values from a fixed range (With criteria), but i need it to do this across 4 sheets

The Sheets are "WRS P1", "WRS P2", "WRS P3" and "WRS P4" Column A has a range of Dates (A8:A32) (This range has a formula in all cells within range, but only some may actually be populated with a value) I need these dates added to the Combobox when the cell in column E is empty The dates in column A will fill the above range in WRS P1 first, then once the range is full, it will flow to WRS P2 in the same range, then to WRS P3 and so on

I need some code to cycle through the range in the first sheet (WRS P1), Once the code reaches an empty cell in column A (Within the range) then the code can stop

I have code to do this on 1 sheet, but i cannot seem to get it to continue looking on the next sheet

This is the code i currently have:

Any assitance would be greatly appreciated!

Dim rngEmpD As Range
Dim rngListD As Range
Dim strSelectedD As String
Dim LastRowD As Long

            strSelectedD = ""
          
            LastRowD = Worksheets("WRS P1").Range("A8:A32").Find("", , xlValues, , , xlNext, , , False).Row
 
           Set rngListD = Worksheets("WRS P1").Range("E8:E" & LastRowD - 1)
 
           For Each rngEmpD In rngListD
 
                 If rngEmpD.Value = strSelectedD Then
 
                      Me.ComboBox1.AddItem rngEmpD.Offset(, -3)
                    
 
                 End If
 
           Next rngEmpD

Answer 1

Have a go with the code below.

We basically add the 4 sheets to an array and loop through the array to get the sheet names. Then we loop through each row in each sheet to add the items to the combobox.

Dim i As Long, j As Long, arr As Variant

arr = Array("WRS P1", "WRS P2", "WRS P3", "WRS P4")

For i = LBound(arr) To UBound(arr)
    With Sheets(arr(i))
        For j = 8 To 32
            If .Range("B” & j).Value = "" Then Exit For
            If .Range("E" & j).Value = "" Then
                Me.ComboBox1.AddItem .Range("B" & j).Value
            End If
        Next j
    End With
Next i