Date Formatting issue

[ajm]

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.

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

 

[Norie]

Are you trying to put NOV 11 in the cell or an actual date?

If it's NOV 11 format the cell as Text before putting the value in.

.Range("A1").NumberFormat = "@"
.Range("A1).Value = NewShtData

 

[ajm]

hi norie,

I want cell a1 on the new sheet to contain the date 1/12/2011 (1 December). this is the first day of the next month from the current sheet's date. the format should be "mmm yy" so that it actually displays as "Dec 12". The weird thing is that stepping through the code, the variable "NewShtDate" correctly holds the date as 1/12/2011. but when the value is written to the cell it changes to 12/01/2011 (12 Jan).

 

[ajm]

i got it while adding something else:

Private Sub CommandButton1_Click()
Dim ThisShtName As Date, NewShtName As String, NewShtDate As Date
Dim NextMth As Integer, ThisYear As Integer
Dim c As Date
Dim OldSht As Worksheet
  
Set OldSht = ThisWorkbook.ActiveSheet


ThisShtName = OldSht.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")
  
  OldSht.Copy after:=Sheets("First")
    On Error Resume Next
        With ActiveSheet
        .Name = NewShtName
        .Range("a1").Value = NewShtDate
        End With
        
    On Error GoTo 0
    
    With OldSht
    .Shapes(1).Delete
    End With
End Sub

i think the difference was that in this version i have declared the variable "NewShtDate" as a date. previously, i had overlooked the fact that it was not declared.

thanks for having a look in anyways.

ajm