date string as sheetname

[keyser_Soze]

I have a macro that copies the last sheet to a new sheet at the end. I
would like to note the date that is in A2 prior to the copy, and add
one month to it.
This result will then become the name of the new sheet in the form
1-Oct-05 or something equivalent (ie: 1 Oct 05, 1Oct05,...)

How can I get the date from A2, and how can I modify it to the correct
string?

Thanks.

 

[Norman Jones]

Hi Keyser,

Try:
'==================>>
Sub Tester()
Dim WB As Workbook
Dim SH As Worksheet
Dim sStr As String
Dim i As Long

Set WB = ActiveWorkbook '<<======== CHANGE

i = WB.Sheets.Count

Set SH = WB.Sheets(I)

With SH.Range("A2")
sStr = DateSerial(Year(.Value), Month(.Value) + 1, 1)
End With

WB.Sheets.Add after:=SH
ActiveSheet.Name = Format(sStr, "d-mmm-yy")

End Sub
'==================>>

 

[keyser_Soze]

Thanks!