You can get the number of worksheets in the book at any time with a line like
this:

NumSheets = Workbooks("SummaryBook.xls").Worksheets.Count

If the number of "other worksheets" is constant, you can just subtract that.
Chart sheets are not included in the count of Worksheets.

You say
... a variable hold the value of NumSites so I can see the value?
I have tried declaring a variable as
Check_Numsites = NumSites
in the public declaration modual but this fails.

This line

Do While NumSites < Cnt

should be

Do While i < Cnt

Similarly,
For Cnt = 1 To NumSites

should be

For Cnt = 1 to i

Monitor the value of i.


On 26 Jan 2005 11:51:30 -0800, (Greg) wrote:

I have a workbook that I import data into and then manipulate the
data. For each worksheet added I wish to keep a counter of the total
data entry worksheets as I have several other worksheets and graphs as
well.

Currently I simply update a variable that has a public declaration -
NumSites
ie Public Const NumSites As Integer = 19

Modual 1 shows how I use the variable.

However, I wish to automate the process. All worksheets entered are
to the left of a file called FileNames.

I have a Menu added, with macro's so anyone can add more worksheets.

1. What is the most effective way to update the file list. I
currently run a fuction, in Modual 2 where I have declared all my
Public variables and the fucntion that counts the number of worksheets
each time NumSites.

2. I also wish to view the variable NumSites whilst debugging - is
there a way of having a variable hold the value of NumSites so I can
see the value? I have tried declaring a variable as Check_Numsites =
NumSites in the public declaration modual but this fails.

Thank you



------------------------------------------------------------------------------
==================== Modual 2 ========================================
Option Explicit

Public FileName As Variant
Public ChartName As Variant

Public Const FileLocation As String _
= "C:\$user\Assessments\GOHS audits\"


Public Const Tot_Rows As Integer = 187
'======================================
Public Function NumSites()
Dim Numsites_cnt As Integer
Dim WS_public_cnt
For Each WS_public_cnt In Worksheets
Do While Not WS_public_cnt.Name = "File names"
Numsites_cnt = Numsites_cnt + 1
Exit Do
Loop
Next WS_public_cnt

End Function

Modual 1 ================================================== =======

Sub M1()
MsgBox "This will go through all the worksheets and calculate
compliance as a %" _
& Chr(13) & "in the background and display the percentages as a
group" _
& Chr(13) & "All worksheets are then displayed in outstanding item
order"
Dim last_site As String
Dim Cnt As Integer
Dim Answer As String
Dim Message As String
Dim Title As String
'Dim vbOkCancel As Integer
Dim i As Integer
i = NumSites

Sheets("File names").Select
[a1].Select
Range(Selection, Selection.End(xlDown)).Rows.Select
Cnt = Range(Selection, Selection.End(xlDown)).Rows.Count
Message = ActiveCell.Offset(1, 0)
Do While NumSites < Cnt
Answer = MsgBox("Do you want to delete upto the following " &
Message, vbOkCancel, Title)
If Answer = vbOK Then
Range(Cells(NumSites + 1, 1), Cells(Cnt, 4)).Select
End If
Exit Do
Loop

For Cnt = 1 To NumSites
Worksheets(Cnt).Select
Call CalcCompliance
Next Cnt
Call addPercentateForGroups
'Makes the groups for the graphs replaces ANZaveCalc
Call M2
Worksheets("Percentage").Select

End Sub