Weird Date change when copying a Worksheet into a new workbook

Question

When I use the following code to create a new workbook with a copied sheet there is a strange date change that occurs:

Worksheets("Week Template").Copy
Set Newwb = ActiveWorkbook
NewwbName = ActiveWorkbook.Name

Windows("Mainworkbook.xlsm").Activate
Sheets("Week Template").Select
    Range("B3:B13").Select
    Application.CutCopyMode = False
    Selection.Copy

Workbooks(NewwbName).Worksheets("Week Template").Activate
Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

For example, if I copied the date "1/1/2023" it is changed to "12/31/2018"

however, when I change the format of the cells to text, the numbers are the same, "43465"

Does anyone know how I can resolve this issue?

Answer 1

Try  this:

    Dim wb As Workbook, wbNew As Workbook, wsTempl As Worksheet
    
    Set wb = ThisWorkbook 'for example, if it's the workbook where this code runs
    Set wsTempl = wb.Worksheets("Week Template")
    
    wsTempl.Copy
    Set wbNew = ActiveWorkbook 'has the copied sheet
    wbNew.Worksheets(wsTempl.Name).Range("B3:B13").Value = wsTempl.Range("B3:B13").Value