I have a series of macros that depend upon counting a number of
spreadsheets. I wish to determine the exact number prior to runing
the macros. I currently update the variable Numsites manually as a
constant. I tried to automate the process so that prior to to running
any other macros I would know the number of spreadsheets I was dealing
with.

The code below had the following error Compile error: Invalid outside
procedure.

Can you please assist with some options.


Option Explicit
Option Private Module

Public Const NumSites As Integer = 20

Dim NumSites As Integer
For Each Worksheet In Worksheets
Do While Not Worksheet.Name = "File names"
NumSites = NumSites + 1
Exit Do
Loop
Next Worksheet