Private Sub CommandButton1_Click()
my_row
End Sub
'==============
Option Explicit
Dim lr
Sub LastRow_In_sheet(sh_Name)
On Error Resume Next
lr = sh_Name.Cells.Find(What:="*", _
After:=Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlValues, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
If Err.Number <> 0 Then lr = ""
On Error GoTo 0
End Sub
'===============================================
Sub my_row()
Dim i%
Dim lr1
Dim k%: k = Sheets.Count
Sheets("sheet1").Activate
lr1 = Sheets("sheet1").Cells(Rows.Count, 1).End(3).Row
If lr1 = 1 Then lr1 = 2
Sheets("sheet1").Range("a2:b" & lr1).ClearContents
If k = 1 Then
Sheets("sheet1").Range("b2") = Sheets("sheet1").Name
Sheets("sheet1").Range("a2") = 2
Exit Sub
End If
For i = 2 To Sheets.Count
Call LastRow_In_sheet(Sheets(i))
Sheets("sheet1").Range("b" & i) = Sheets(i).Name
If IsNumeric(lr) Then
Sheets("sheet1").Range("a" & i) = lr
Else
Sheets("sheet1").Range("a" & i) = " (Empty)"
End If
Next
End Sub