PDA

View Full Version : Solved: Naming Worksheets



zoom38
04-04-2006, 04:22 PM
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.

Sub NewWorksheets()
Application.ScreenUpdating = False
worksheetName$ = ActiveSheet.Name

Sheets(ActiveWorkbook.Sheets.Count).Select
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "new sheet" '??????
End Sub

Any help is appreciated.
Thanks
Gary

XLGibbs
04-04-2006, 05:23 PM
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


ActiveSheet.Name = Sheets("SheetNames").Range("A1")


then delete that cell and shift up..


Sheets("SheetNames").Range("A1").Delete Shift: = xlup


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

zoom38
04-04-2006, 06:42 PM
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
04-04-2006, 06:58 PM
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

zoom38
04-04-2006, 07:46 PM
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
04-04-2006, 08:09 PM
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
04-04-2006, 08:23 PM
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
04-04-2006, 08:33 PM
try

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

lucas
04-04-2006, 09:17 PM
Welcome to VbaExpress jindon. I see we have another RegExp guru..

jindon
04-04-2006, 09:24 PM
Steve,

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

jindon

lucas
04-04-2006, 09:56 PM
I saw one of your dictionary object posts here (http://vbaexpress.com/forum/showpost.php?p=61784&postcount=9) . Looks like you have lots of areas of expertise....glad to have you visiting the forum.

zoom38
04-05-2006, 07:34 AM
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
04-05-2006, 07:53 AM
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. :thumb

Gary

lucas
04-05-2006, 11:31 AM
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
04-05-2006, 05:02 PM
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?


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


Thanks
Gary

jindon
04-05-2006, 05:18 PM
change Replace function to worksheetfunction.substitute


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

zoom38
04-05-2006, 05:51 PM
jindon, you are a genius, it worked perfectly. Thank you for all the help.


Gary