Hello,
Earlier I was working on the part of the macro that created a sheet and named it from cell values, then created a new workbook and named it the same as the created sheet to save in a specified location.
Now I'm trying to script a method to go back to the original workbook and delete the newly created sheet after the new workbook is saved.
Full macro code:
I'm showing where I set declarations because I'm wondering if they aren't right but the bottom set is where the debugger sends me:
Thanks,
Shawn
Earlier I was working on the part of the macro that created a sheet and named it from cell values, then created a new workbook and named it the same as the created sheet to save in a specified location.
Now I'm trying to script a method to go back to the original workbook and delete the newly created sheet after the new workbook is saved.
Full macro code:
Code:
Sub Cloud()
'=============================================================================================================
'This macro asks the user if they are connected to the VPN. If they are, the sheet will transfer data to a new
'sheet and name it based on the data in cell AM3. AM3 is concatenated L2,AJ2, AJ3, and E5. This lets each sheet
'uploaded have a name unique to the auditor, but still classified by division, month, and week of month.
'
'If the user attempts to create a dataset a week in advance, they are denied.
'If the user attempts to create a second set of data in the same week without using a fresh sheet, they are denied
'
'If the user is not connected to the VPN, macro ends without doing anything and the user is asked to connect
'before attempting again.
'
'=============================================================================================================
Dim ws As Worksheet, chkShtName As String
Dim wbDATS As Workbook 'DATS Workbook
Dim wsDATS As Worksheet 'DATS Worksheet
Dim wsDELETE As Worksheet
'!AN2 holds version number data update is important and below must reflect the ver change
Set wbDATS = Workbooks("DATS v. 5.0a.xlsm")
' Refers to the deletion process as which sheet to make active
Set wsDATS = wbDATS.Worksheets("DATS")
'VPN connection question
MSG1 = MsgBox("Are you currently connected to the VPN?", vbYesNo + vbQuestion, "VPN CONNECTION CHECK")
'Set variable to value in DATS!AM3
chkShtName = Sheets("DATS").Range("AM3")
If MSG1 = vbYes Then
MsgBox "Please remain connected until this task is complete."
'If chkShtName is empty, notify and exit
'chkShtName should never be empty it pulls from concatenated data in cell AM3, but data can be incomplete and handled later.
If chkShtName = "" Then
MsgBox "CRITICAL ERROR: AM3 DATA MISSING" & vbNewLine & vbNewLine & _
"Please use the 'Help Ticket' function in the upper right of the DATS form and inform of an 'AM3 Data Missing' error."
Exit Sub
End If
'If chkShtName (data in AM3) is not empty, check to see if Sheet exists.
'chShtName is concatenated information from 4 cells.
'The Set instruction will produce an error if the sheet does not exist.
'If sheet doesn't exist, it is "nothing"
On Error Resume Next
Set ws = Sheets(chkShtName)
On Error GoTo 0
'If ws is Not Nothing then it exists and tell the user, exit sub
If Not ws Is Nothing Then
MsgBox "You can't create the same week of data twice in the same Workbook."
Exit Sub
End If
'add sheet named from data in cell AM3
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = chkShtName
'Turn off screen updating and alert messages to hide activity
Application.ScreenUpdating = False
Application.DisplayAlerts = False
'label headers
Range("A1").FormulaR1C1 = "Auditor"
Range("B1").FormulaR1C1 = "Date"
Range("C1").FormulaR1C1 = "Store"
Range("D1").FormulaR1C1 = "Arrive"
Range("E1").FormulaR1C1 = "Start"
Range("F1").FormulaR1C1 = "Stop"
Range("G1").FormulaR1C1 = "Depart"
Range("H1").FormulaR1C1 = "Drive"
Range("I1").FormulaR1C1 = "Research"
Range("J1").FormulaR1C1 = "Audit"
Range("K1").FormulaR1C1 = "Close"
Range("L1").FormulaR1C1 = "Retail"
Range("M1").FormulaR1C1 = "Speed"
Range("N1").FormulaR1C1 = "Month"
Range("O1").FormulaR1C1 = "Week"
'format cells
Range("B2:B7").NumberFormat = "m/d/yy;@"
Range("D2:G7").NumberFormat = "[$-409]h:mm AM/PM;@"
Range("A2").FormulaR1C1 = "=IF(DATS!R5C5="""","""",DATS!R5C5)"
Range("A2").AutoFill Destination:=Range("A2:A7"), Type:=xlFillDefault
Range("B2").FormulaR1C1 = "=DATS!R[32]C[2]"
Range("B2").AutoFill Destination:=Range("B2:B7"), Type:=xlFillDefault
'transpose data
Range("C2").FormulaR1C1 = "=DATS!R[23]C[1]"
Range("C3").FormulaR1C1 = "=DATS!R[22]C[3]"
Range("C4").FormulaR1C1 = "=DATS!R[21]C[5]"
Range("C5").FormulaR1C1 = "=DATS!R[20]C[7]"
Range("C6").FormulaR1C1 = "=DATS!R[19]C[9]"
Range("C7").FormulaR1C1 = "=DATS!R[18]C[11]"
Range("D2").FormulaR1C1 = "=DATS!R[13]C"
Range("D3").FormulaR1C1 = "=DATS!R[12]C[2]"
Range("D4").FormulaR1C1 = "=DATS!R[11]C[4]"
Range("D5").FormulaR1C1 = "=DATS!R[10]C[6]"
Range("D6").FormulaR1C1 = "=DATS!R[9]C[8]"
Range("D7").FormulaR1C1 = "=DATS!R[8]C[10]"
Range("E2").FormulaR1C1 = "=DATS!R[26]C[-1]"
Range("E3").FormulaR1C1 = "=DATS!R[25]C[1]"
Range("E4").FormulaR1C1 = "=DATS!R[24]C[3]"
Range("E5").FormulaR1C1 = "=DATS!R[23]C[3]"
Range("E5").FormulaR1C1 = "=DATS!R[23]C[5]"
Range("E6").FormulaR1C1 = "=DATS!R[22]C[7]"
Range("E7").FormulaR1C1 = "=DATS!R[21]C[9]"
Range("F2").FormulaR1C1 = "=DATS!R[26]C[-1]"
Range("F3").FormulaR1C1 = "=DATS!R[25]C[1]"
Range("F4").FormulaR1C1 = "=DATS!R[24]C[3]"
Range("F5").FormulaR1C1 = "=DATS!R[23]C[5]"
Range("F6").FormulaR1C1 = "=DATS!R[22]C[7]"
Range("F7").FormulaR1C1 = "=DATS!R[21]C[9]"
Range("G2").FormulaR1C1 = "=DATS!R[12]C[-2]"
Range("G3").FormulaR1C1 = "=DATS!R[11]C"
Range("G4").FormulaR1C1 = "=DATS!R[10]C[2]"
Range("G5").FormulaR1C1 = "=DATS!R[9]C[4]"
Range("G6").FormulaR1C1 = "=DATS!R[8]C[6]"
Range("G7").FormulaR1C1 = "=DATS!R[7]C[8]"
Range("H2").FormulaR1C1 = "=DATS!R[16]C[-4]+DATS!R[16]C[-3]"
Range("H3").FormulaR1C1 = "=DATS!R[15]C[-2]+DATS!R[15]C[-1]"
Range("H4").FormulaR1C1 = "=DATS!R[14]C+DATS!R[14]C[1]"
Range("H5").FormulaR1C1 = "=DATS!R[13]C[2]+DATS!R[13]C[3]"
Range("H6").FormulaR1C1 = "=DATS!R[12]C[4]+DATS!R[12]C[5]"
Range("H7").FormulaR1C1 = "=DATS!R[11]C[6]+DATS!R[11]C[7]"
Range("I2").FormulaR1C1 = "=(RC[-4]-RC[-5])*24"
Range("I2").AutoFill Destination:=Range("I2:I7"), Type:=xlFillDefault
Range("I2:I7").AutoFill Destination:=Range("I2:K7"), Type:=xlFillDefault
Range("L2").FormulaR1C1 = "=DATS!R[24]C[-8]"
Range("L3").FormulaR1C1 = "=DATS!R[23]C[-6]"
Range("L4").FormulaR1C1 = "=DATS!R[22]C[-4]"
Range("L5").FormulaR1C1 = "=DATS!R[20]C[-2]"
Range("L6").FormulaR1C1 = "=DATS!R[20]C"
Range("L5").FormulaR1C1 = "=DATS!R[21]C[-4]"
Range("L5").FormulaR1C1 = "=DATS!R[21]C[-2]"
Range("L6").FormulaR1C1 = "=DATS!R[20]C"
Range("L7").FormulaR1C1 = "=DATS!R[19]C[2]"
Range("M2").FormulaR1C1 = "=DATS!R[22]C[-8]"
Range("M3").FormulaR1C1 = "=DATS!R[21]C[-6]"
Range("M4").FormulaR1C1 = "=DATS!R[20]C[-4]"
Range("M5").FormulaR1C1 = "=DATS!R[19]C[-2]"
Range("M6").FormulaR1C1 = "=DATS!R[18]C"
Range("M7").FormulaR1C1 = "=DATS!R[17]C[2]"
Range("N2").FormulaR1C1 = "=DATS!R2C36"
Range("N2").AutoFill Destination:=Range("N2:N7"), Type:=xlFillDefault
Range("N2:N7").NumberFormat = "mmmm"
Range("O2").FormulaR1C1 = "=DATS!R[1]C[21]"
Range("O2").FormulaR1C1 = "=DATS!R3C36"
Range("O2").AutoFill Destination:=Range("O2:O7"), Type:=xlFillDefault
Range("O2:O7").Select
Range("B1:M7").Select
Range("M1").Activate
Selection.Cut Destination:=Range("D8:O14")
Range("N1:O7").Select
Selection.Cut Destination:=Range("B1:C7")
Range("D8:O14").Select
Selection.Cut Destination:=Range("D1:O7")
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
'format cell size to accomodate the data
Cells.EntireRow.AutoFit
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
Application.DisplayAlerts = True
ActiveSheet.Copy
'make new workbook from new sheet
'Thanks to This is My Answer on Mr. Excel for the tips in this step
Range("A1:O7").Copy
With ActiveSheet.UsedRange
.PasteSpecial xlValues
.PasteSpecial xlFormats
End With
'replace below with networked drive and folder address
ChDir "C:\Users\xxxxx\Desktop"
Application.CutCopyMode = False
ActiveWorkbook.SaveAs Filename:="C:\Users\xxxxx\Desktop\" & chkShtName, _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
' go back to DATS workbook, DATS page and delete the created page covertly
Set wbDATS = ActiveWorkbook
Set wsDATS = ActiveSheet
Set wsDELETE = wbDATS.Worksheets(chkShtName)
Application.DisplayAlerts = False
wsDATS.Activate
wsDELETE.Delete
Application.DisplayAlerts = True
Else
MsgBox "Connecting to the VPN is critical to this task." & vbNewLine & _
vbNewLine & "Please connect and try again or save this sheet and upload your data when you're able to connect." & vbNewLine & _
vbNewLine & "If you're unable to connect for an extended period because of network issues or travel," & _
" please notify your Division Manager and Senior Auditors."
End If
End SubI'm showing where I set declarations because I'm wondering if they aren't right but the bottom set is where the debugger sends me:
Code:
'Dim
Dim ws As Worksheet, chkShtName As String
Dim wbDATS As Workbook 'DATS Workbook
Dim wsDATS As Worksheet 'DATS Worksheet
Dim wsDELETE As Worksheet
'!AN2 holds version number data update is important and below must reflect the ver change
'DATS v.X.Xy is the original workbook
Set wbDATS = Workbooks("DATS v. 5.0a.xlsm")
' Refers to the deletion process as which sheet to make active
Set wsDATS = wbDATS.Worksheets("DATS")
'macro
' go back to DATS workbook, DATS page and delete the created page covertly
Set wbDATS = ActiveWorkbook
Set wsDATS = ActiveSheet
Set wsDELETE = wbDATS.Worksheets(chkShtName)
Application.DisplayAlerts = False
wsDATS.Activate
wsDELETE.Delete
Application.DisplayAlerts = TrueThanks,
Shawn
