Excel 2007 : macro to count sheet records

**nlm** · 10-13-2011, 06:33 AM

Hi,

I have multiple sheets in a workbook. Sheet1 is Totalrecords sheet contains all the sales data from different retailers. Rest sheets are named as per retailer's name e.g. John, James.

First I import all sales data in Totalrecords sheet from where I paste retailers data in their respective sheet.

To verify that each retailer has the same amount of data pasted from Totalrecords sheet, I apply count function in each sheet manually. With the passage of time, our retailers base increase. Will there be any macro which can count and display the number of records in each retailer sheet. I want to display just the number of records in each retailer sheet.

I am attaching a dummy file for your convenience.

Any help would be higly appreciated.

Thanks.

**Nikeyg** · 10-13-2011, 07:16 AM

I've attached what I think you need and what I would do.

On the front sheet I have a total for each Retailer across the top facilitated by a Countif function for their names against column B.

I've put in a rather untidy macro, that automates your copy and paste to the individual sheets for you, as I am relatively new it's rather untidy. I'm sure one of the more experienced guys here could make it better.

Sub Macro1()

    Sheet2.Select
    Range("A3").Select
    Sheet3.Select
    Range("A3").Select
    Sheet4.Select
    Range("A3").Select
    Sheet1.Select
    Range("B:B").Select
    For Each Cell In Selection
    If Cell.Value = "James" Then
    Cell.EntireRow.Copy
    Sheet2.Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Next Cell

    Sheet1.Select
    Range("B:B").Select
    For Each Cell In Selection
    If Cell.Value = "John" Then
    Cell.EntireRow.Copy
    Sheet3.Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Next Cell
    
    Sheet1.Select
    Range("B:B").Select
    For Each Cell In Selection
    If Cell.Value = "Sophie" Then
    Cell.EntireRow.Copy
    Sheet4.Select
    ActiveCell.Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End If
    Next Cell
End Sub

Open it up and take a look.

**nlm** · 10-13-2011, 09:06 AM

Awesome!!!!!! It worked.

Actually I posted to just count records for James, John and Sophie. I have no idea that it could have been possible. Thx a lot.

Can you please modify a bit so that I am able to count records for James, John and Sophie like James=5 records, John=3 records and Sophie=15 records and so on....

Thanks.

**Nikeyg** · 10-13-2011, 09:07 AM

Originally Posted by nlm

Can you please modify a bit so that I am able to count records for James, John and Sophie like James=5 records, John=3 records and Sophie=15 records and so on....

Thanks.

I don't quite understand this part.
Please elaborate.
I've put the Countif on each tab, if you don't touch it, and your retailer data is in column B each time they'll do your counting automatically for you.