Macro or VBA to collect title and number of lines from each sheet in a workbook

[OhioRuss]

I have workbooks that contain multiple sheets (tabs). I would like to have a macro that creates a new sheet called "Inventory" that contains the name of each of the other sheets and the number of lines it contains. Can someone help with that, please?

Russ

[Marc L]

As a beginner starter :

Sub Demo1()
  Const W = "Inventory"
    Dim L&, R&
        L = Sheets.Count
        Application.ScreenUpdating = False
    If Sheets(L).Name <> W Then
        Sheets.Add(, Sheets(L)).Name = W
        L = L + 1
    Else
        Sheets(L).UsedRange.Clear
    End If
    For R = 1 To L - 1
        Sheets(L).Cells(R, 1).Resize(, 2).Value = Array(Sheets(R).Name, Sheets(R).UsedRange.Rows.Count)
    Next
        Application.ScreenUpdating = True
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[OhioRuss]

Merci, Marc. Ça marche très bien!

[PCI]

See next code, it works where ever rows

Please Login or Register  to view this content.

[xladept]

Try this:

Please Login or Register  to view this content.

[Marc L]

… with Inventory as first worksheet, as a beginner starter :

Sub Demo2()
  Const W = "Inventory"
    Dim R&
        Application.ScreenUpdating = False
        If Sheets(1).Name <> W Then Sheets.Add(Sheets(1)).Name = W Else Sheets(1).UsedRange.Clear
        Sheets(1).[A1:B1].Value = [{"Name","Rows count"}]
    For R = 2 To Sheets.Count
        Sheets(1).Cells(R, 1).Resize(, 2).Value = Array(Sheets(R).Name, Sheets(R).UsedRange.Rows.Count)
    Next
        Application.ScreenUpdating = True
End Sub  

Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !

[Marc L]

My bad, misread …

[PCI]

An update to make things smarter

Please Login or Register  to view this content.

[Marc L]

Merci, Marc. Ça marche très bien!

Thanks for the rep' and your french words !

[OhioRuss]

Thank you all. Marc's solution works, I haven't tried the others. Anyone care to comment on the relative merits?

[Marc L]

Salut PCI !

Your code using CurrentRegion may return a wrong value if a worksheet has an empty row between two range of data …

Another concern : Find will raise an error with an empty worksheet !

[xladept]

@ OhioRuss - You're best situated to discuss the relative merits

[PCI]

@Marc
Yes, thank you for the comments which are setting some limits to others macros to.
So here a remake:
The first row can be every where
Sheet can be empty
Some empty rows may exist inside the used range: The empty row counted in the total
It seems these specifications are out of the OP's needs it is just to close the loop

Please Login or Register  to view this content.