Date Formatting issue

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
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.

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
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
.Range("A1").NumberFormat = "@"
.Range("A1).Value = NewShtData
 

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
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

Well-known Member
Joined
Feb 5, 2003
Messages
1,808
i got it while adding something else:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,187
Messages
5,640,693
Members
417,161
Latest member
Devon150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top