CODEWITHSUNDEEP
excelvbacountmacrospaste
simen
simen

Reputation: 1

Count rows and copy number

I have an excel workbook with multiple sheets with tables in the range from A to L. I want to count the rows and paste the result into a summary sheet.

For example:

Sheet2 has 3 rows. I want to copy the number of rows into sheet1 C3

Sheet3 has 9 rows. I want to copy the number of rows into sheet1 C4

Sheet4 has 5 rows. I want to copy the number of rows into sheet1 C5

and so on through all my sheets. (I have over 3000 sheets)

I am new to macro in excel so I would appreciete any help, thanks so much.

Upvotes: 0

Views: 1452

Answers (2)

Rajan
Rajan

Reputation: 147

Hope this simple macro helps:

Sub coutndisplay()

    Dim count As Integer

    ' Create an object for sheet 1
    Set sh1 = ThisWorkbook.Sheets(1)

    ' Get the number of sheets in the workbook
    count = ThisWorkbook.Sheets.count

    For i = 2 To count

        ' Populate the number of rows in sheet 1 starting from C3
        ' C3 populate the number of rows in sheet 2 and it goes on
        sh1.Cells(i + 1, 3) = ThisWorkbook.Sheets(i).UsedRange.Rows.count
    Next i

End Sub

Upvotes: 0

xQbert
xQbert

Reputation: 35323

No macro needed just us a formula:

=COUNTA(Sheet2!A:A) changing sheet2 to the approrpiate sheet for each cell in C3, C4, C5

Put simply...put

=COUNTA(Sheet2!A:A) in C3

=COUNTA(Sheet3!A:A) in C4

=COUNTA(Sheet4!A:A) in C5

Assuming Column A in each sheet has a value when you want to count the row, otherwise use a different column! (changing A:A to B:B or something else)

If the names aren't sheet1 etc... you could....

Create this defined name: Formulas tab > Name Manager > New

  • Name: SheetNames
  • Scope: Workbook
  • Refers to: =GET.WORKBOOK(1)&T(NOW())
  • click OK

Then add this formula to B instead of the numbers

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

Fill down and now you have the values to fill in your c3 and beyond

Upvotes: 2

Related Questions