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

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

Dim iReports as Integer

iReports = 0

For i = 1 to WB.Worksheets.Count

If Instr(WB.Worksheets(i).name, "Report") Then iReports = iReport
+ 1

Next

--
Kaa
-----------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...nfo&userid=751
View this thread: http://www.excelforum.com/showthread.php?threadid=55726

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

Freddie Mac wrote:
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!


Hi Freddie

You could use a For... Next Loop and include

If Left(sh.Name,6) = "Report" Then
shtcount = shtcount + 1

Report shtcount at the end of your routine

Regards

Steve

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

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

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

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

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