Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,665
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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
Rich (BB code):
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