Dim WS As Worksheet
Dim ReportCount As Long
For each WS In ThisWorkbook.Worksheets
If Instr(WS.Name, "Report) 0 Then
ReportCount=ReportCount+1
End If
Next

NickHK

"Freddie Mac" wrote in message
...
I have a macro that generates worksheets. Each new worksheet must of
course
have an individual unique name. however every new worksheet that i
generate
have the same beginning of name i.e. Report. The sub that generates a new
worksheet is run one time every time the program runs. After that the
program
ends. The program may then be ran again and new worksheets are generated.
Therefore using static is not possible. Now I count the worksheets in the
worksbook and assign the worksheet with a constant name (Report) and a
varaible e.g. Report 1, Report 2 etc. The problem is that counting
worksheets
is not good because I have number of worksheets that are not reports and
so
the first report gets a higher number than 1. The number of worksheets
that
are not Reports may vary. Is there a way of counting worksheets that have
the
same name beginning (i.e. counting all worksheets that have name starting
with Report) or is there any other way of solving the problem? Please help
me!

The code is:
iCtr = WB.Worksheets.Count
Set sh = Worksheets.Add(after:=Worksheets(iCtr))
sh.Name = sName & iCtr
Call workSheetSettings(sh.Name)
x = sh.Name