Marhier
10-22-2017, 11:33 PM
Hello everyone.
I've made a form that relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
I've used an On Error code for when they haven't copied the data from the other location; I have it bring up a message box that says 'Copy data first!' and then exits the sub.
The issue I'm having is, when there isn't an error and the code completes correctly, the message box still appears.
Could someone let me know where I'm going wrong; how do I make it so that message only comes up when the error occurs, and not when the code completes correctly as well?
My code is now as follows:
Sub Create()
Application.ScreenUpdating = False
On Error GoTo Errhandler
If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select
Errhandler:
MsgBox "Copy data first!"
Sheets("Home").Visible = xlSheetVisible
Sheets("Nexus Report").Visible = xlSheetVeryHidden
Exit Sub
Application.ScreenUpdating = True
End Sub
All help is greatly appreciated.
Thank you.
Regards
Martin
I've made a form that relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
I've used an On Error code for when they haven't copied the data from the other location; I have it bring up a message box that says 'Copy data first!' and then exits the sub.
The issue I'm having is, when there isn't an error and the code completes correctly, the message box still appears.
Could someone let me know where I'm going wrong; how do I make it so that message only comes up when the error occurs, and not when the code completes correctly as well?
My code is now as follows:
Sub Create()
Application.ScreenUpdating = False
On Error GoTo Errhandler
If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Sheets("DMSL").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1").Select
Sheets("DMSL").Select
Sheets("DMSL").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Rows("13:13").Select
ActiveSheet.Range("$A$12:$AC$10000").AutoFilter Field:=1, Criteria1:="<>"
Range("U3:V4").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[10]C[3]:R[9997]C[3])"
Range("A1").Select
Application.Dialogs(xlDialogSaveAs).Show
MsgBox "Copy saved. The copy will now close." _
& vbCrLf _
& myFile
ActiveWorkbook.Close
Range("A1").Select
Sheets("Nexus Report").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Cells.Select
Selection.ClearContents
Range("A1").Select
Sheets("Home").Visible = xlSheetVisible
ActiveSheet.Visible = xlSheetVeryHidden
Range("A1:U4").Select
Errhandler:
MsgBox "Copy data first!"
Sheets("Home").Visible = xlSheetVisible
Sheets("Nexus Report").Visible = xlSheetVeryHidden
Exit Sub
Application.ScreenUpdating = True
End Sub
All help is greatly appreciated.
Thank you.
Regards
Martin