Option Explicit
Sub Summary_Sheet_Counter()
'https://www.mrexcel.com/forum/excel-questions/1077636-getting-rows-worksheets.html
Dim LR As Variant
Dim WS As Worksheet
Dim WS_Count As Integer
Dim i As Integer, j As Integer
Application.ScreenUpdating = False
'Delete Summary Sheet
On Error Resume Next
Worksheets("[COLOR=#ff0000]Summary[/COLOR]").Activate
On Error GoTo 0
If ActiveSheet.Name = "[COLOR=#ff0000]Summary[/COLOR]" Then
Application.DisplayAlerts = False
Worksheets("[COLOR=#ff0000]Summary[/COLOR]").Delete
Application.DisplayAlerts = True
End If
WS_Count = ActiveWorkbook.Sheets.Count
'Create New Summary Sheet
Worksheets.Add Before:=Worksheets(1)
ActiveSheet.Name = "[COLOR=#ff0000]Summary[/COLOR]"
'Count last row in column A across all sheets
For i = 2 To WS_Count + 1
LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, "A").End(xlUp).Row
With Sheets("[COLOR=#ff0000]Summary[/COLOR]")
.Cells(i, 1).Value = Sheets(i).Name
.Cells(i, 2).Value = LR
End With
Next i
Application.ScreenUpdating = True
End Sub