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

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

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

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