Quick Sheet Rename Question VBA

[Siyanna]

Hi Guys,

I need some code that will rename the sheet to the sheet before the active sheet + 7

What i am doing is copying a template sheet before the compatibilty Report sheet.
I am then trying to rename the sheet to the sheet before this active sheet + 7
The previous sheet say is like this 20.06.2012
I would want to rename the active sheet/Copied template sheet 27.06.2012

This is as far as i have got

Sub AddSheets()
Dim Ts As Worksheet
Set Ts = Sheets("Template")
Ts.Copy before:=Sheets("Compatibility Report")
Activesheet.name = ?????

End Sub

 

[Aadhi]

Hi,

Please search your quote in this form you will get the answer...

 

[Siyanna]

Hi,

Please search your quote in this form you will get the answer...

Hi,

Sorry, i am not sure what you mean?

 

[Siyanna]

Hi,

Sorry, i am not sure what you mean?

Hi All,

The Compatibility Report sheet is always the last sheet therefore the second last sheet is where the is copied to

I tried this but no luck

Please Help Me

Sub AddSheets()
Dim Ts As Worksheet
Set Ts = Sheets("Template")
Ts.Copy before:=Sheets("Compatibility Report")
ActiveSheet.Name = Sheets(Sheets.Count - 2).Name + 7

End Sub

 

[Siyanna]

Hi All,

The Compatibility Report sheet is always the last sheet therefore the second last sheet is where the is copied to

I tried this but no luck

Please Help Me

Sub AddSheets()
Dim Ts As Worksheet
Set Ts = Sheets("Template")
Ts.Copy before:=Sheets("Compatibility Report")
ActiveSheet.Name = Sheets(Sheets.Count - 2).Name + 7

End Sub

Hi All,

I apologise if i need to post now in a specific way. If i posting correctly ignore this message. If not, please guide me to the new posting Rules

 

[Aadhi]

Hi,

You can use this code to find the sheet name

Public Function SheetNames_Find()
Dim Arr() As String
Dim i As Integer
ReDim Arr(Sheets.Count - 1)
For i = 0 To Sheets.Count - 1
Arr(i) = Sheets(i + 1).Name
Sheet_Name = Arr(i)
If (Sheet_Name = "Sheet Name") Then
Sheet_Name = Arr(i)
Exit For
End If
Next i
SheetNames_Find = Arr
SheetNames_Find = Application.WorksheetFunction.Transpose(Arr)
End Function

you can set the array sheet counter as you wish.....

 

[Siyanna]

Hi,

Can anyone provide any solution without looping. Thank You

 

[andrewkard]

Hello
ActiveSheet.Name = DateValue(Sheets(Sheets.Count - 2).Name) + 7

 

[andrewkard]

If i understand you correctly

 

[hitch_hiker]

do you need the sheet to be renamed to the date the page is copied , or to a date you need to specify ? e.g. if you copy the sheet on the 20/6/12 the sheet is named 2012.6.20 then next week the sheet would be named 2012.6.27 etc, or if you copy the sheet 10 times today you need it to go 2012.6.20 , 2012.6.27 , 2012.7.4 , 2012.7.11 etc?