Add statistic data using VBA

I am using VBA to analyse and create numerous worksheets. I need to add a worksheet and the insert the name from each sheet and the count of items [excluding the header row]

i.e.

Column A            Column B
Sheet 1                  250
Sheet 2                    75
Sheet 3                1450
Sheet 4                  600

etc

Could an expert provide me with the VBA code to do this please

Thanks in Advance
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Haris DulicCommented:
Hello,

try this:

Sub count()
For i = 1 To ActiveWorkbook.Sheets.count
Me.Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Me.Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
If Err Then
Me.Range("B" & i) = 0
End If
On Error GoTo 0
Next i

End Sub

Open in new window

0
JagwarmanAuthor Commented:
samo4fun

I am getting invalid use of Me Keyword
0
Haris DulicCommented:
Can you put the sheet name where you want the data to be written? i.e. Sheet1 instead of Me?
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

JagwarmanAuthor Commented:
I tried that but got Object required. Having said that There can be 20 or 30 sheets depending on the days data so I cannot hard code that?
0
Haris DulicCommented:
Are you opening the file where you count the sheets and object or you are appending the new sheet and saving the results there?

Can you provide more detail steps on process so that i get clear picture? ;)
0
JagwarmanAuthor Commented:
My macro opens two files and then performs various functions. It sets up new Sheets for each Name in column F and copies over all rows with that respective name.

so today there may be 10 different names so 10 new sheets with their respective names tomorrow maybe 8 next day when the file is run maybe 30 etc

I then create a sheet call stats and on this sheet I need to  insert the name from each sheet and then count of items [excluding the header row]

 i.e.

 Column A            Column B
 Sheet 1                  250
 Sheet 2                    75
 Sheet 3                1450
 Sheet 4                  600

Regards
0
Haris DulicCommented:
OK... in the code after you create sheet "stats" you can add updated version of the code:

For i = 1 To ActiveWorkbook.Sheets.count
Sheets("stats").Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
If Err Then
Sheets("stats").Range("B" & i) = 0
End If
On Error GoTo 0
Next i

Open in new window

0
JagwarmanAuthor Commented:
I appreciate your help and apologise for keep returning :-)

Above code listed the sheets but...... all but one of the sheets in the totals it put 0 and in one it put 32

Sheet 1      0
Sheet 2      0
Sheet 3      0
Sheet 4      32
Sheet 5      0


in fact the totals should be

Sheet 1      0
Sheet 2      57
Sheet 3      28
Sheet 4      14
Sheet 5      14
0
Haris DulicCommented:
No problems, as long as it takes to solve this issue...

Did you change the range in this line :

Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count

since it is only observing the column A and i presume that there is data in other columns so maybe you can change it to something like this :

Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:AA").Cells.SpecialCells(xlCellTypeConstants).count
0
JagwarmanAuthor Commented:
Hi Samo4fun

Again apologies for the delay, been busy on other stuff.

Just tried this on another workbook and get

Sheet 1      0
 Sheet 2      0
 Sheet 3      0
 Sheet 4      0
 Sheet 5      0


should be

Sheet 1       0
 Sheet 2      1685
 Sheet 3      382
 Sheet 4      0
 Sheet 5      0

I only need to count in column A. Does it matter what is in the cell i.e. Text, Number, Date etc?

Thanks
Regards
0
Haris DulicCommented:
Hello,

can you try this.. I just tested it and it works ok..

For i = 1 To ActiveWorkbook.Sheets.count
Sheets("stats").Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.count - WorksheetFunction.CountBlank(Worksheets("Sheet" & i).Range("A:A"))
If Err Then
Sheets("stats").Range("B" & i) = 0
End If
On Error GoTo 0
Next i

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
Hi samo4fun

it's something to do with my workbook. I copied all of the data onto a new workbook and it worked. Very odd. So thanks for all your work on this I will sign this one off now and look at my workbook to see if I can sus out the problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.