Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through sheets and get number of records per sheet
Hi all, i have a variable number of sheets in a workbook, with variable rows
of data in each sheet. Each sheet has a specific name. I need to loop through all sheets and get the number of rows per sheet and store the number of rows per sheet/name on a summary sheet. e.g. George 20 Les 150 Johan 60 Any help is as per normal much appreciated -- Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through sheets and get number of records per sheet
Sub test()
Dim summ As Worksheet Dim ws As Worksheet Set summ = Worksheets.Add summ.Name = "Summary" Dim x As Integer For Each ws In Worksheets If ws.Name < "Summary" Then summ.Range("a1").Offset(x, 1).Value = ws.Cells.SpecialCells(xlCellTypeLastCell).Row summ.Range("a1").Offset(x, 0).Value = ws.Name x = x + 1 End If Next ws End Sub "Les" wrote: Hi all, i have a variable number of sheets in a workbook, with variable rows of data in each sheet. Each sheet has a specific name. I need to loop through all sheets and get the number of rows per sheet and store the number of rows per sheet/name on a summary sheet. e.g. George 20 Les 150 Johan 60 Any help is as per normal much appreciated -- Les |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through sheets and get number of records per sheet
A Gentelman and a life saver....
Thanks so much Sam, much appreciated. -- Les "Sam Wilson" wrote: Sub test() Dim summ As Worksheet Dim ws As Worksheet Set summ = Worksheets.Add summ.Name = "Summary" Dim x As Integer For Each ws In Worksheets If ws.Name < "Summary" Then summ.Range("a1").Offset(x, 1).Value = ws.Cells.SpecialCells(xlCellTypeLastCell).Row summ.Range("a1").Offset(x, 0).Value = ws.Name x = x + 1 End If Next ws End Sub "Les" wrote: Hi all, i have a variable number of sheets in a workbook, with variable rows of data in each sheet. Each sheet has a specific name. I need to loop through all sheets and get the number of rows per sheet and store the number of rows per sheet/name on a summary sheet. e.g. George 20 Les 150 Johan 60 Any help is as per normal much appreciated -- Les |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through sheets and get number of records per sheet
Hi
I've assumed your Summary Sheet is sheet1 and that your data is a continuous range starting at A1 on each sheet. Should get you started. Sub countrows() Dim i As Integer, SheetCount As Integer Dim RowCounts() As Variant Application.Screenupdating = false SheetCount = ActiveWorkbook.Worksheets.Count - 1 'don't count SummarySheet ReDim RowCounts(1 To SheetCount, 1 To 2) For i = 2 To SheetCount + 1 'assume sheet1 is SummarySheet RowCounts(i - 1, 1) = Worksheets(i).Name RowCounts(i - 1, 2) = Worksheets(i).Range("A1").CurrentRegion.Rows.Count Next i Worksheets("SummarySheet").Range("A2").Resize(Shee tCount,2).Value = RowCounts End Sub regards Paul On May 20, 4:13*pm, Les wrote: Hi all, i have a variable number of sheets in a workbook, with variable rows of data in each sheet. Each sheet has a specific name. I need to loop through all sheets and get the number of rows per sheet and store the number of rows per sheet/name on a summary sheet. e.g. George * * 20 Les * * * * *150 Johan * * * 60 Any help is as per normal much appreciated -- Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop through sheets & filter main sheet using sheet name | Excel Programming | |||
Loop to Filter, Name Sheets. If Blank, Exit Loop | Excel Programming | |||
Number of Sheets (Full of Sheet) | Excel Discussion (Misc queries) | |||
How to know the total number of records in the excel sheet | Excel Programming | |||
Loop across Sheets and number of sheets | Excel Programming |