How to count number of rows in multiple sheet names in a file and produce summary table

[jk51]

Hi,

I am using Excel 2010. Do you know VBA excel macro code that counts the number of rows in multiple sheet names in a excel workbook file and produce summary table in a new sheet called "Summary" : Sheet name and their counts. Note all the multiple sheets the header fields are all the same.

Thank you.

Mr Singh

 

[Gireesh]

Hi Singh,

Hope! the code below will help you. I didn't write the code to create a sheet "Summary". I did it manually.

Option Explicit

Sub Summary_Dashboard()

Dim LR As Variant
Dim WS As Worksheet
Dim WS_Count As Integer
Dim i As Integer, j As Integer

WS_Count = ActiveWorkbook.Sheets.Count
For i = 2 To WS_Count
  LR = ActiveWorkbook.Sheets(i).Cells(Rows.Count, 1).End(xlUp).Row - 1
  With Sheets("Summary")
  .Cells(i, 2).Value = LR
  .Cells(i, 1).Value = Sheets(i).Name
  End With
Next i

End Sub

 

[jk51]

Thank you Gireesh.

The code is working unless you add a blank sheet manually and type "Summary" first.

I want to add the sheet automatically called "Summary" after running the macro.


Thank you

Mr Singh

 

[jk51]

Hi Gireesh,

Is it possible to add another column in the summary table, count number of duplicate rows in multiple sheet names in a excel workbook file and produce summary table in a new sheet called "Summary": with 3 columns Sheet name and their counts and their duplicate counts. I want the header fields display on the summary table.

Thank you

Mr Singh

 

[jk51]

Help please in VBA excel codes 2010.
How to add another column in the summary table, count number of unique rows in multiple sheet names in a excel workbook file and produce summary table in a new sheet called "Summary": with 3 columns Sheet name and their counts and their unique counts. I want the header fields display on the summary table.
Thank you.
Mr Singh

 

[Gireesh]

Hi Singh,

Sorry for the delay....Give me sometime....