mismatch error when using sheets in array to populate data on userform

[Mussala]

Hello ,
I try to populate data for all of sheets in listbox on userform , but shows mismatch error in this line

r = Sheets(sh).Range("A1").CurrentRegion.Value

Dim r As Variant


Sub LoadData()
Dim sh As Variant
For Each sh In Sheets(Array("Mussala", "mssau", "mjhgsg"))
r = Sheets(sh).Range("A1").CurrentRegion.Value
Next sh
 With Me.ListBox1
        .List = r
End With

End Sub
Private Sub UserForm_Initialize()
Call LoadData
  
End Sub

I hope some body fix this problem .

 

[Fuji]

    For Each e In Array("Mussala", "mssau", "mjhgsg")
        s(0) = s(0) & IIf(s(0) = "", "", "Union All (") & "Select " & _
        "Format(`DATE`,'dd/mm/yyyy'), `INVOICE NO`, `TYPE`, `DEBIT`, `CREDIT`, " & _
        "`BALANCE` From `" & e & "$` Where `TYPE` <> 'OPENING' And `TYPE` Is Not Null " & IIf(s(0) = "", "", ") ")
    Next

 

[Mussala]

perfect !
finally ,may you show headers in first row in listbox?
like
DATE, INVOICE NO, TYPE, DEBIT`, CREDIT, " BALANCE"

 

[Fuji]

Don't want to make it too complex and no benefit to experiencing such for me, so PLEASE ask someone else.

 

[Mussala]

Don't want to make ot too complex and no benefit to experiencing such for me, so PLEASE ask someone else.

my apologies!
I hope to getting assistance from you again to next thread with more clear details .
many thanks for your assistance.