Put number next to Sheet name
 

Hi all, I want macro which should add WorkSheet and then check if
there are more WorkSheets in ActiveWorkbook, and if there are more
WorkSheets then check if any of those WorkSheet got name "Data". If
there is no WorkSheet with name "Data" then put name "Data" to new
added WorkSheet but if there is a WorkSheet with name "Data" then put
name "Data(1)" of new added WorkSheet, and if there are WorkSheets
with names "Data" and "Data(1)" then put name "Data(2)" of new added
WorkSheet and so on like (3),(4).... I hope i was able to explain what
i need. Please can any friend can help

Put number next to Sheet name
 

Dim i As Long
Dim shName As String
Dim baseName As String

baseName = "Data"
shName = baseName
On Error Resume Next
Worksheets.Add
ActiveSheet.Name = shName
Do While ActiveSheet.Name < shName

i = i + 1
shName = baseName & "(" & i & ")"
ActiveSheet.Name = shName
Loop
On Error GoTo 0


--
__________________________________
HTH

Bob

"K" wrote in message
...
Hi all, I want macro which should add WorkSheet and then check if
there are more WorkSheets in ActiveWorkbook, and if there are more
WorkSheets then check if any of those WorkSheet got name "Data". If
there is no WorkSheet with name "Data" then put name "Data" to new
added WorkSheet but if there is a WorkSheet with name "Data" then put
name "Data(1)" of new added WorkSheet, and if there are WorkSheets
with names "Data" and "Data(1)" then put name "Data(2)" of new added
WorkSheet and so on like (3),(4).... I hope i was able to explain what
i need. Please can any friend can help

Put number next to Sheet name
 

On Jan 19, 11:22*am, "Bob Phillips" wrote:
Dim i As Long
Dim shName As String
Dim baseName As String

* * baseName = "Data"
* * shName = baseName
* * On Error Resume Next
* * Worksheets.Add
* * ActiveSheet.Name = shName
* * Do While ActiveSheet.Name < shName

* * * * i = i + 1
* * * * shName = baseName & "(" & i & ")"
* * * * ActiveSheet.Name = shName
* * Loop
* * On Error GoTo 0

--
__________________________________
HTH

Bob

"K" wrote in message

...



Hi all, *I want macro which should add WorkSheet and then check if
there are more WorkSheets in ActiveWorkbook, and if there are more
WorkSheets then check if any of those WorkSheet got name "Data". *If
there is no WorkSheet with name "Data" then put name "Data" to new
added WorkSheet but if there is a WorkSheet with name "Data" then put
name "Data(1)" of new added WorkSheet, and if there are WorkSheets
with names "Data" and "Data(1)" then put name "Data(2)" of new added
WorkSheet and so on like (3),(4).... I hope i was able to explain what
i need. *Please can any friend can help- Hide quoted text -

- Show quoted text -

Thanks lot Bob your code work superb

Put number next to Sheet name
 

Bob answered you question, but just a bit of clarification...

and then check if
there are more WorkSheets in ActiveWorkbook,

A workbook must contains at least one worksheet and at least one sheet
must be visible. There is no such thing as a workbook without any
worksheets. Thus, the test "if there are more worksheets in
ActiveWorkbook" is unnecessary. Since the must be at least one sheet
to begin with, there will necessarily be "more worksheets" after a new
sheet is added.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Mon, 19 Jan 2009 02:52:56 -0800 (PST), K
wrote:

Hi all, I want macro which should add WorkSheet and then check if
there are more WorkSheets in ActiveWorkbook, and if there are more
WorkSheets then check if any of those WorkSheet got name "Data". If
there is no WorkSheet with name "Data" then put name "Data" to new
added WorkSheet but if there is a WorkSheet with name "Data" then put
name "Data(1)" of new added WorkSheet, and if there are WorkSheets
with names "Data" and "Data(1)" then put name "Data(2)" of new added
WorkSheet and so on like (3),(4).... I hope i was able to explain what
i need. Please can any friend can help