Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to lookup a sheet, number, then display in origonal sheet | New Users to Excel | |||
Controlling sheet number during sheet add | Excel Programming | |||
find an exact number in a different sheet and use the cell containing the number to output information | Excel Programming | |||
Using the Indirect function with a sheet number instead of a sheet name | Excel Worksheet Functions | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions |