Dibbley247
02-07-2013, 08:19 AM
I have a couple of workbooks which when a macro button it is, it will save a copy and clear it down ready for new week.
I'm due to have a week off work soon, and my manager gets really worried about things shes never done before. so I would like to trick her a little when I'm next off.
Here is a sample of part of one of my codes with help from p45CAL!
Could I sneak in AFTER the macros has finished.
A box will pop up (preferably with a loading line) saying "deleting hard drive" with a cancel button, then when the cancel button is hit a message then saying "drive is corrupt, please contact I.T"
:rotlaugh:
Sub CleardownSheets()
Dim Response As Integer
Response = MsgBox(prompt:="This will clear the sheet for a new week, are you sure?", Buttons:=vbYesNo)
If Response = vbYes Then
'Will now clear ranges if YES was selected
Const PATH As String = "\\calfp03\Lynher Bakery\1AAMan Hours\current week\2013 - A1\1A WK"
With ActiveWorkbook
.SaveCopyAs Filename:=PATH & .Sheets("Reset Sheet").Range("A1").Value & ".xls"
sApplication.ScreenUpdating = False
' Will clear all sheets in workbook
Sheets("Sat Night (3)").Select
Range("B5,D5:K5,O5:P5,B7,D7:K7,O7:P7,B9,D9:K9,O9:P9,B11,D11:K11,O11:P11,D13:K13,O1 3:P13").Select Selection.ClearContents
Application.ScreenUpdating = True
ActiveWorkbook.Save
End With
Else
' The no button was selected.
MsgBox "Action Was Cancelled"
End If
End Sub
I'm due to have a week off work soon, and my manager gets really worried about things shes never done before. so I would like to trick her a little when I'm next off.
Here is a sample of part of one of my codes with help from p45CAL!
Could I sneak in AFTER the macros has finished.
A box will pop up (preferably with a loading line) saying "deleting hard drive" with a cancel button, then when the cancel button is hit a message then saying "drive is corrupt, please contact I.T"
:rotlaugh:
Sub CleardownSheets()
Dim Response As Integer
Response = MsgBox(prompt:="This will clear the sheet for a new week, are you sure?", Buttons:=vbYesNo)
If Response = vbYes Then
'Will now clear ranges if YES was selected
Const PATH As String = "\\calfp03\Lynher Bakery\1AAMan Hours\current week\2013 - A1\1A WK"
With ActiveWorkbook
.SaveCopyAs Filename:=PATH & .Sheets("Reset Sheet").Range("A1").Value & ".xls"
sApplication.ScreenUpdating = False
' Will clear all sheets in workbook
Sheets("Sat Night (3)").Select
Range("B5,D5:K5,O5:P5,B7,D7:K7,O7:P7,B9,D9:K9,O9:P9,B11,D11:K11,O11:P11,D13:K13,O1 3:P13").Select Selection.ClearContents
Application.ScreenUpdating = True
ActiveWorkbook.Save
End With
Else
' The no button was selected.
MsgBox "Action Was Cancelled"
End If
End Sub