Hi Freddie,

Repalce the suggested code with the following version:

'=============
Private Sub worksheetMaker()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report"

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then
iCtr = iCtr + 1
End If
Next i
'
'If iCtr = 0 Then iCtr = 1
Set SH = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
SH.Name = sName & iCtr + 1
End Sub
'<<=============

--
---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi Freddie,

In tthat case try instead:

'=============
Private Sub worksheetMaker()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report"

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then
iCtr = iCtr + 1
End If
Next i

If iCtr = 0 Then iCtr = 1
Set SH = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
SH.Name = sName & iCtr + 1
End Sub
'<<=============


---
Regards,
Norman


"Freddie Mac" wrote in message
...
Yes Thank you it works better.....However I have a problem with this
code, it
does not work for the first report i.e. when there is no sheet named
Report
1. How do you solve that? Please help me on this one!

"Norman Jones" skrev:

Hi Freddie,

Try:

Public Sub mainProgram()
Call worksheetMaker

End Sub

Private Sub worksheetMaker()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report"

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then
iCtr = iCtr + 1
End If
Next i

Set SH = Worksheets.Add(after:=Worksheets(sName & iCtr))
SH.Name = sName & iCtr + 1
End Sub


---
Regards,
Norman



"Freddie Mac" wrote in message
...
Great that seems like a very fine code! However when I try to use it
the
program says "Error! Next without For" and it highlights the sub where
i
try
to run the code.
I copied your code but I submit it anyhow in case there is something
missing.

Public Sub mainProgram()
Call worksheetMaker
...................
End Sub

Private Sub worksheetMaker()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report "

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then
iCtr = iCtr + 1
Next i

Set SH = Worksheets.Add(after:=Worksheets(sName & iCtr + 1))
SH.Name = sName & iCtr
End Sub

If you have any idea how to solve this please post an answer! Thank
you!

"Norman Jones" skrev:

Hi Freddie,

Try:

'=============
Public Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim i As Long
Dim iCtr As Long
Const sName As String = "Report"

For i = 1 To ThisWorkbook.Worksheets.Count
If Worksheets(i).Name Like sName & "*" Then iCtr = iCtr + 1
Next i

Set SH = Worksheets.Add(after:=Worksheets(sName & iCtr + 1))
SH.Name = sName & iCtr

End Sub
'<<=============


--
---
Regards,
Norman



"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