Summary of worksheets

[James]

I have several workbooks with multiple worksheets associated with them (on
the range of 70-90 worksheets). Is there way to create a worksheet that will
show me what each worksheet name is and how many rows of data is in each
worksheet.

The amount of data never exceeds what is in column A.

Thanks

 

[Billy Liddel]

I have several workbooks with multiple worksheets associated with them (on
the range of 70-90 worksheets). Is there way to create a worksheet that will
show me what each worksheet name is and how many rows of data is in each
worksheet.

The amount of data never exceeds what is in column A.

Thanks

James

You have to use a macro. insert a sheet at the from of the workbook, copy
this code into a VB module (ALT + F11, Insert, Module) return to sheet and
run the code. (ALT + F8) select macro & Run

Sub listSHNames()
' List sheet Names in Worksheet 1
Dim ref As String, i As Integer, r As Integer
Dim lastRow As Long
With ActiveWorkbook
Worksheets(1).Select
r = 2
'get worksheet names
For i = 2 To Sheets.count
Cells(r, 1).Activate
With Worksheets(i)
lastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Cells(r, 1) = Worksheets(i).Name
Cells(r, 2) = lastRow
r = r + 1
End With
Next i
End With

End Sub


Regards
Peter

 

[Don Guillett]

Try
Sub showrowspersheet()
For i = 1 To Sheets.Count
Cells(i, "k") = Sheets(i).Name
Cells(i, "l") = Sheets(i).UsedRange.Rows.Count
Next i
End Sub

 

[Don Guillett]

Use this instead
Sub showrowspersheet()
For i = 1 To Sheets.Count
Cells(i, "k") = Sheets(i).Name
'Cells(i, "l") = Sheets(i).UsedRange.Rows.Count
Cells(i, "m") = Sheets(i).Cells.SpecialCells(xlCellTypeLastCell).Row

Next i
End Sub

 

[James]

Don,
This worked great, but can you help me with a minor adjustment to this code.
I would like the summary to show up on a complete new worksheet. Hope you
don't mind helping.

Thanks

 

[Billy Liddel]

I have altered the macro I sent previously to add a new sheet. It will delete
any previous Summary sheet hence the extra code.

Sub AddSummarySheet()
Dim Wks As Worksheet, Headers, r As Integer
Set Wks = Worksheets(1)
Application.DisplayAlerts = False
'delete Summary sheet if it exists
If Wks.Name = "Sheet Summary" Then
Wks.Delete
End If
Sheets.Add
'reset wks reference to new sheet
Set Wks = Worksheets(1)
'add headers on new sheet and format
Headers = Array("Sheet Name", "Last Cell", "Last Column")
Application.Goto Wks.Range("A1")
Range("A1:C1") = Headers
Range("A1:C1").Select
Selection.Font.Bold = True
Columns("A:C").EntireColumn.AutoFit
ActiveSheet.Name = "Sheet Summary"
'begin listing sheet data
r = 2
For i = 1 To Sheets.count
With Worksheets(i)
lastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
lastColumn = .Cells.SpecialCells(xlCellTypeLastCell).Column
Cells(r, 1) = Worksheets(i).Name
Cells(r, 2) = lastRow
'Cells(r, 3) = lastColumn
r = r + 1
End With
Next i

End Sub

I remmed out the Last column - just remove the apostrophe if you decide to
keep it.

Peter

 

[Don Guillett]

Easy enough. Use the last cell as offered by Billy

Sub showrowspersheet()
Sheets.Add
ActiveSheet.Name = "Summary_New"

For i = 1 To Sheets.Count
Cells(i, "a") = Sheets(i).Name
'Cells(i, "l") = Sheets(i).UsedRange.Rows.Count
Cells(i, "b") = Sheets(i).Cells.SpecialCells(xlCellTypeLastCell).Row

Next i
End Sub

 

[James]

Thank Don and Billy both options worked great.

Easy enough. Use the last cell as offered by Billy

Sub showrowspersheet()
Sheets.Add
ActiveSheet.Name = "Summary_New"

For i = 1 To Sheets.Count
Cells(i, "a") = Sheets(i).Name
'Cells(i, "l") = Sheets(i).UsedRange.Rows.Count
Cells(i, "b") = Sheets(i).Cells.SpecialCells(xlCellTypeLastCell).Row

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)