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" wrote:

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

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
Microsoft MVP Excel
SalesAid Software

"Billy Liddel" wrote in message
...


"James" wrote:

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