I have pieced to gether the following code which should copy the active sheet and then rename (both Tab name and sheet title should change) the sheet.
My active sheet is called "NOV 11" and this date also appears in cell A1 of each active sheet. All works fine except when it gets to updating the value in A1 on the newly created sheet. for some reason, the date gets turned around to US style mm/dd/yy instead of staying as aussie/UK style of ddd/mm/yyy.
what am i missing or what can i do to get the right date?
regards,
ajm
Code:
Private Sub CommandButton1_Click()
Dim ThisShtName As Date, NewShtName As String
Dim NextMth As Integer, ThisYear As Integer
Dim c As Date
ThisShtName = ActiveSheet.Range("a1").Value
NextMth = Month(ThisShtName) + 1
ThisYear = Year(ThisShtName)
If NextMth = 13 Then NextMth = 1: ThisYear = ThisYear + 1 ' Special code for December
NewShtDate = Format("1/" & NextMth & "/" & ThisYear, "dd/mm/yy") '///for cell A1
NewShtName = Format(NextMth & "/" & ThisYear, "mmm yy")
ActiveSheet.Copy after:=Sheets("First")
On Error Resume Next
With ActiveSheet
.Name = NewShtName
.Range("a1") = NewShtDate
End With
On Error GoTo 0
End Sub
My active sheet is called "NOV 11" and this date also appears in cell A1 of each active sheet. All works fine except when it gets to updating the value in A1 on the newly created sheet. for some reason, the date gets turned around to US style mm/dd/yy instead of staying as aussie/UK style of ddd/mm/yyy.
what am i missing or what can i do to get the right date?
regards,
ajm