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

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