Sheet Name Macro Problems

[carlito_1985]

Hey everyone, I'm working on a sheet where it has all the months down the bottom, and a summary tab. I want the worksheet so when I click a button for the next year, it automatically changes it from "July 05" to "July 06" for each one. I did a pretty dodgy recorded macro and it worked initially, but for the next year It came up with an error because it still looks for sheet "July 05" instead of sheet "July 06". Is there any way to fix this or a better technique for doing this?? thanks guys, btw, ive attached an original copy of the DB and one with the macro. Thanks again

[mudraker]

Carlito

Try this on a back up copy of your work book

I have re written your code - You will need to replace your code with mine


I have coded it when re nameing sheets to add 1 to the last 2 digits of the existing sheet name (Does not change summary sheet) and enter the same formula to each sheet



Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/04/2005 by AGizariotis
' Modified by Mudraker 27/4/2005

'
Dim Ws As Worksheet
Dim sName As String
Dim sFormula As String

iYear = Format(Year(Now()) + 1, "yy")
For Each Ws In Worksheets
If Ws.Name <> "SUMMARY" Then
sName$ = Left(Ws.Name, Len(wsname) - 2)
Ws.Name = sName & Format(Right(Ws.Name, 1) + 1, "yy")
Ws.Columns("T:T").NumberFormat = "@"
Ws.Range("T7").FormulaR1C1 = "=RIGHT(T5,2)"
Ws.Range("S7").FormulaR1C1 = "=RIGHT(R[-2]C[1],2)"
Ws.Range("S9").FormulaR1C1 = "=R[-2]C+1"
Ws.Range("S10").FormulaR1C1 = "=""0""&R[-1]C"
sFormula$ = "=" & sName & " &R[-1]C"
Ws.Range("S11").FormulaR1C1 = sFormula
Ws.Range("P3:U17").ClearContents
End If
Next Ws
Sheets("SUMMARY").Select
Range("A1:G1").Select
End Sub

[mudraker]

Carlito

I left a . out of the code
sName$ = Left(Ws.Name, Len(wsname) - 2)
should have been
sName$ = Left(Ws.Name, Len(wWs.Name) - 2)

I also spotted an error in this line
Ws.Name = sName & Format(Right(Ws.Name, 1) + 1, "yy")
should be
Ws.Name = sName & Format(Right(Ws.Name, 2) + 1, "00")

This line is also no longer required
iYear = Format(Year(Now()) + 1, "yy")


Try this version of the macro

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/04/2005 by AGizariotis
' Modified by Mudraker 27/4/2005

'
Dim Ws As Worksheet
Dim sName As String
Dim sFormula As String

For Each Ws In Worksheets
If Ws.Name <> "SUMMARY" Then
sName$ = Left(Ws.Name, Len(Ws.Name) - 2)
Ws.Name = sName & Format(Right(Ws.Name, 2) + 1, "00")
Ws.Columns("T:T").NumberFormat = "@"
Ws.Range("T7").FormulaR1C1 = "=RIGHT(T5,2)"
Ws.Range("S7").FormulaR1C1 = "=RIGHT(R[-2]C[1],2)"
Ws.Range("S9").FormulaR1C1 = "=R[-2]C+1"
Ws.Range("S10").FormulaR1C1 = "=""0""&R[-1]C"
sFormula$ = "=" & sName & " &R[-1]C"
Ws.Range("S11").FormulaR1C1 = sFormula
Ws.Range("P3:U17").ClearContents
End If
Next Ws
Sheets("SUMMARY").Select
Range("A1:G1").Select
End Sub