Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summary count from different worksheets? | Excel Discussion (Misc queries) | |||
summary page of many worksheets | Excel Discussion (Misc queries) | |||
Summary page for 12 worksheets | Excel Discussion (Misc queries) | |||
Summary list of worksheets in workbook | Excel Discussion (Misc queries) | |||
Spawning worksheets and a summary per worksheet | Excel Discussion (Misc queries) |