Summary of worksheets
 

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

Summary of worksheets
 

"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

Summary of worksheets
 

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

Summary of worksheets
 

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

"Don Guillett" wrote:

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

Summary of worksheets
 

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

"James" wrote:

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

"Don Guillett" wrote:

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

Summary of worksheets
 

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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

"Don Guillett" wrote:

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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

Summary of worksheets
 

Thank Don and Billy both options worked great.

"Don Guillett" wrote:

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"James" wrote in message
...
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

"Don Guillett" wrote:

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).R ow

Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
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