Accurate Data Not Being Passed During Copy

[Ark68]

Consider this code of a macro used to create a new workbook using copied contents of a 2nd workbook's worksheets, than save the new workbook with a defined name.

Sub save_dataset()
    MsgBox "Data is locked. Procced with dataset save.", , "DATASET SAVE"
    Dim control_data As Worksheet 'destination worksheet
    With Workbooks("dispatch.xlsm").Worksheets("Data")
        .Unprotect
        .Range("E3:E" & .Range("B" & Rows.Count).End(xlUp).Row).Formula = "=$A$3&TEXT(ROW()-2,""000"")" 'Record ID$ 'populate recordID$ with {serial date vale(A26)+ row number as 3 digits}
        .Protect
    End With
    Application.DisplayAlerts = False 'Eliminate accidental duplication
    On Error Resume Next
    ThisWorkbook.Sheets("CONTROL_1").Delete
    ThisWorkbook.Sheets("Staff").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "CONTROL_1"
    Set control_data = Worksheets("CONTROL_1")
    With Worksheets("Data")
        .Unprotect
        If .FilterMode Then .ShowAllData
        .Protect
        .Columns("e").Copy
        With control_data
            .Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        End With
        .Range("A1:D1").EntireColumn.Copy
        Dim LR As Long
        LR = Range("A" & Rows.Count).End(xlUp).Row
        With Range("a2:a" & LR)
            .Value = .Value
        End With
        Worksheets("CONTROL_1").Range("B1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
        .Columns("F:DI").Copy
        Worksheets("CONTROL_1").Range("F1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    End With
    With control_data
        .Rows("1:1").Delete
        .Cells.EntireColumn.AutoFit
        Dim llastrow As Long
        llastrow = .Range("a65536").End(xlUp).Row
        Worksheets("Reference").Range("U39").Copy
        With .Range("A2:DI" & llastrow)
            .PasteSpecial xlFormats
        End With
        .Range("B:B, S:S, Z:Z").NumberFormat = "dd-mmm-yy"
        .Range("n:n, o:o, u:u, ab:ab, aw:aw, ba:ba, bd:bd, bh:bh, bi:bi, bm:bm, bo:bo, bu:bu, bw:bw, cc:cc, ce:ce, ck:ck, cm:cm, df:df, dg:dg, dh:dh, di:di").NumberFormat = ("h:mm am/pm")
        .Columns("l").NumberFormat = ("@")
        .Columns("l:l").TextToColumns Destination:=Range("L1"), DataType:=xlDelimited, _
        textqualifier:=xlDoubleQuote, consecutivedelimiter:=False, Other:=False, fieldinfo:=Array(1, 2), trailingminusnumbers:=True
        .Protect
    End With
    Dim fName As String
    With control_data.Range("b2")
       fName = Format(.Value, "00000") & "(" & Format(.Value, "dd-mmmm-yy") & ").xlsx"
    End With
    With Worksheets("StaffMaster")
       .Visible = True
       .Unprotect
       .Range("$A$1") = Worksheets("varhold").Range("A26").Value
       .Protect
    End With
    Workbooks.Add
    Sheets("Sheet1").Name = "CONTROL_1"
    Sheets.Add After:=Sheets(Sheets.Count)
    Sheets("Sheet2").Name = "Staff"
    With control_data
        .Cells.Copy
        With ActiveWorkbook.Worksheets("CONTROL_1")
            .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Cells.EntireColumn.AutoFit
        End With
    End With
    With Workbooks("Dispatch.xlsm").Worksheets("StaffMaster")
        .Cells.Copy
        With ActiveWorkbook.Worksheets("Staff")
            .Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            .Cells.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
    End With
    'Sheets(Array("CONTROL_1", "StaffMaster")).Copy
    'Sheets("StaffMaster").Name = "Staff"
    With Worksheets("CONTROL_1")
        .Unprotect
        .Range("DJ1") = "Peak"
        .Range("DK1") = "gm_doit"
        .Range("DL1") = "sig_doit"
        .Range("DM1") = "lights_eligible"
        .Range("DN1") = "lts_para1"
        .Range("DO1") = "lights_ON"
        .Range("DP1") = "lts_para2"
        .Range("DQ1") = "lights_OFF"
        .Range("DR1") = "close_doit"
        .Range("DS1") = "close_date"
        .Range("DT1") = "rel1_doit"
        .Range("DU1") = "rel2_doit"
        .Range("DV1") = "rel3_doit"
        .Range("DW1") = "rel4_doit"
        .Range("DX1") = "<empty2>"
        .Range("DY1") = "<empty3>"
        .Range("DZ1") = "review_flag"
        .Range("EA1") = "wshrms_doit"
        .Range("EB1") = "wshrms_para1"
        .Range("EC1") = "wshrms_timeopen"
        .Range("ED1") = "wshrms_nameopen"
        .Range("EE1") = "wshrms_para2"
        .Range("EF1") = "wshrms_closetime"
        .Range("EG1") = "wshrms_nameclose"
        .Range("EH1") = "notes"
        .Protect
    End With
    'remove names from core data
    'Dim i As Long
    'Dim Nm As Name
    'For Each Nm In ActiveWorkbook.Names
    'If Nm.Name <> "dderange" Then
    '    Nm.Delete
    'End If
    'Next
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\Data\" & fName
    ActiveWorkbook.Close
    Application.DisplayAlerts = False
    Sheets("CONTROL_1").Delete
    Application.DisplayAlerts = True
    Worksheets("Staffmaster").Visible = False
    MsgBox fName & "Data reference file successfully created. (" & fName & ")"
    CreateDataset.Label1 = fName & "Data reference file successfully created."
    Worksheets("Frontpage").Activate
    'Unload Me
    
End Sub

Both source worksheets, "Data" and "StaffMaster" contain formula based data. The intent is to copy the contents stripped of formula, values and formats only, from the source to new a new workbook with worksheet names "CONTROL_1" and "Staff" respectively.

The contents of "Data" are copied over to "CONTROL_1" without issue. However, the data from "StaffMaster" isn't. Only cell A1 is copied over properly. The rest of the data I have no idea where it has come from. Basically, the data in "StaffMaster" represents names and their shifts for the date in A1. The data in "StaffMaster" is properly calculated, the values are fine for the date of July 17th. However, in the "Staff" worksheet, the date is correct, however, the data represented is not the same as that in "StaffMaster".

I've highlighted the code in the macro I wrote to copy over the "StaffMaster" data. Can anyone comment on what may possibly be the problem?

Jenn