Dear naanu, Please see attached sheet and try below code,
*Sub Sheet_creater()*
*Sheets.Add before:=Sheets("main")
If Sheets.Count = 2 Then
ActiveSheet.Name = "New"
Else
ActiveSheet.Name = "New" & (Sheets.Count) - 2
End If
Sheet1.Activate*
*End Select
*
--
Thanks & regards,
Noorain Ansari
*http://excelmacroworld.blogspot.com/*<http://excelmacroworld.blogspot.com/>
*http://noorain-ansari.blogspot.com/* <http://noorain-ansari.blogspot.com/>
On Sat, Sep 24, 2011 at 4:40 PM, naanu v <naanu2...@gmail.com> wrote:
> I have a workbook with more than 300 sheets and 1 main sheet. I had tried
> to create a macro considering the objective to add a new worksheet before
> the main sheet.
>
> And my macro is below:
>
> ‘Sheets("main").Select
>
> ‘ Worksheets.Add
>
> ‘ ActiveSheet.Name = "new"
>
> I m facing a problem when the “new “sheet already exists. Can u tell any
> possible way that if the sheet name exists, it should consider the next
> succeeding value.
>
> (for eg if sheet à”new” exists, then create “new1” sheet
>
> if sheet à”new1” exists, then create “new2” sheet
>
> if sheet à”new2” exists, then create “new3” sheet
>
> and so on……………………………)
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
> To post to this group, send email to excel-macros@googlegroups.com
>
> <><><><><><><><><><><><><><><><><><><><><><>
> Like our page on facebook , Just follow below link
> http://www.facebook.com/discussexcel
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
NewSheet_Creater_template.xls
Description: MS-Excel spreadsheet
