Loop thru a customer list and go to their shhet and insert data

I have created a excel workbook in which I use for keeping up with storage unit rent. I have a tenant list sheet and each tenant has a sheet on which I keep up with charges and credits. What I want is a macro the will loop thru each tenants sheet based on tenant list and insert data and go to the next sheet. I found this on the web:

Sub Macro21(control As IRibbonControl)
Dim Sheet_Name As Range
For Each Sheet_Name In Sheets("tenant list").Range("A:A")
    If Sheet_Name.Value = "" Then
        Exit For
    Else
     Sheets(Sheet_Name.Value).Select    This is where I get a run time error 9 subscript out of range
        
    Dim emptyrow As Long
emptyrow = WorksheetFunction.CountA(Range("a:a")) + 8

Cells(emptyrow, 1).Value = Date
Cells(emptyrow, 2).Value = "Rent"
Cells(emptyrow, 3).Value = Range("b15")
        
        End If
    Next Sheet_Name


    

End Sub

Any help with be greatly appreciated.