Counting worksheets with a particular name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
'<<=============
 
Freddie said:
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!
Click to expand...

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 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
 
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,

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
'<<=============
 
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
'<<=============
 
Back
Top