Solved: Naming Worksheets

[zoom38]

I have worksheets named "Apr '04 - Mar '05", "Apr '05 - Mar '06" etc. I am trying to write code that will generate a new worksheet and place it at the end and automatically name it the next years ex: "Apr '06 - Mar '07" and so on. I can generate the new sheet but I can't figure out how to name it as above. Is it possible or do I have to use an input box each time I activate the macro to manually generate the new sheet name. Below is the code Im using to generate the new sheet.
[VBA]
Sub NewWorksheets()
Application.ScreenUpdating = False
worksheetName$ = ActiveSheet.Name

Sheets(ActiveWorkbook.Sheets.Count).Select
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "new sheet" '??????
End Sub
[/VBA]
Any help is appreciated.
Thanks
Gary

[XLGibbs]

You could use a list on a hidden sheet to feed the names into your sheets and just delete the name after it is used

Say the list is column a of a sheet called "SheetNames", and the next sheet to be added is in A1

[VBA]
ActiveSheet.Name = Sheets("SheetNames").Range("A1")
[/VBA]

then delete that cell and shift up..

[VBA]
Sheets("SheetNames").Range("A1").Delete Shift: = xlup
[/VBA]

that would be the simplest way without have to do complex evaluations of the sheet names already there.

[zoom38]

Thanks XL, that worked except when I hide the SheetNames worksheet sometimes (not always) I get a runtime error.
Another question. I use the sheet names in formulas how do I use the sheetname in a formula?
ex: cell d1 has the following formula = Apr '05 - Mar '06!H1+1
now if I generate a new sheet with the name "Apr '06 - Mar '07", how do I put this in my formula in cell d1 on the new sheet which should be
=Apr '06 - Mar '07!H1+1 without using the hidden sheet.

[jindon]

[vba]
Sub Test()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Set ws = Sheets.Add(after:=Sheets(Sheets.Count))
ws.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
& Format(myyr + 1, "00")
End If
End With
End Sub[/vba]

[zoom38]

Fantastic code jindon. Can you help me copy the previous worksheet and paste it onto the new one made with your sub? Ive tried but I can't seem to do it.
Thanks
Gary

[jindon]

Is that..

Sub Test()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
    .Pattern = "\'\d{2}$"
    If .Test(wsName) Then
        Set mItem = .Execute(wsName)
        myyr = CInt(Replace(mItem.Item(0), "'", ""))
    Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
    ActiveSheet.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
              & Format(myyr + 1, "00")
    End If
End With
End Sub

[zoom38]

Works fantastic jindon. One last request (I hope). How do I take the sheetname and place it in a formula. I have a cell that references the previous year.
Ex. On the worksheet named "Apr '05 - Mar '06" cell d1 formula is =Apr '04 - Mar '05!H1+1
When I use your code to copy "Apr '05 - Mar '06" and name it "Apr '06 - Mar' 07" i need to update the formula in cell d4 to =Apr '05 - Mar '06!H1+1

Thanks
Gary

[jindon]

try
[vba]
Sub Test()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
& Format(myyr + 1, "00")
.Range("d1").Formula = "='" & _
Replace(wsName, "'", "''") & "'!H1+1"
End With
End If
End With
End Sub[/vba]

[lucas]

Welcome to VbaExpress jindon. I see we have another RegExp guru..

[jindon]

Steve,

Thanks for welcome and I should say, my experty is Dictionary Object...
rather than Regular Expression...

jindon

[lucas]

I saw one of your dictionary object posts here . Looks like you have lots of areas of expertise....glad to have you visiting the forum.

[zoom38]

Thank you jindon fantastic code, works great with one exception. When I invoke the code for the first time I receive

"Run Time Error '1004':

Application-defined or object-defined error"

and it does not update cell d1. Then after manually changing cell d1 I invoked the code a couple more times and no errors come up and it works flawlessly. If you have an idea why the error comes up the first time your code is invoked let me know, otherwise I can deal with it.

Thank you
Gary

[zoom38]

Jindon disreguard my last message. I was getting that error when I was invoking the code from within the viewer. I created a button to invoke the code with the viewer closed and I don't get that error anymore. You are an Excel Expert and thanks for helping this rookie. Problem solved.

Gary

[lucas]

because of the upgrade you cannot mark your threads solved yet. I will mark it for you. Special thanks to jindon for his contributions here.

[zoom38]

Just when I though I had it. It seems jindons code works on Excel 2003 which I have at home but I need it to work on Excel 2000 which is what I have at work. When I activate the code on Excel 2000 I get the following pop up error:

Microsoft Visual Basic

X 400

Can anyone advise on how to avoid this error?

[VBA]
Sub Test()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
& Format(myyr + 1, "00")
.Range("d1").Formula = "='" & _
Replace(wsName, "'", "''") & "'!H1+1"
.Range("a8").Formula = "='" & _
Replace(wsName, "'", "''") & "'!A34"
End With
End If
End With
End Sub
[/VBA]

Thanks
Gary

[jindon]

change Replace function to worksheetfunction.substitute
[vba]

Sub Test()
Dim wsName
wsName = Sheets(Sheets.Count).Name
With CreateObject("VBScript.RegExp")
.Pattern = "\'\d{2}$"
If .Test(wsName) Then
Set mItem = .Execute(wsName)
myyr = CInt(Replace(mItem.Item(0), "'", ""))
Sheets(Sheets.Count).Copy after:=Sheets(Sheets.Count)
With ActiveSheet
.Name = "Apr '" & Format(myyr, "00") & " - Mar '" _
& Format(myyr + 1, "00")
.Range("d1").Formula = "='" & _
application.substitute(wsName, "'", "''") & "'!H1+1"
.Range("a8").Formula = "='" & _
Replace(wsName, "'", "''") & "'!A34"
End With
End If
End With
End Sub

[/vba]

[zoom38]

jindon, you are a genius, it worked perfectly. Thank you for all the help.


Gary