Afternoon all.
Got a slight problem I'd appreciate some help with.
The form I've made relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
Where I'm struggling is figuring out where to put the OnError code when they haven't copied the data from the other location.
I just would like it so if there is nothing copied, it brings up a message box that says 'Copy data first!' and doesn't do anything else.
I've tried the following, but it still continues the code and messes up:
On Error GoTo Errormessage
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit Sub
Errormessage:
MsgBox "Copy data first!"
Here is the code without:
Really appreciate any support on this.
Thank you.
Regards
Martin
Got a slight problem I'd appreciate some help with.
The form I've made relies on someone copying data from a separate location and then clicking on a button that generates and saves as a copy.
Where I'm struggling is figuring out where to put the OnError code when they haven't copied the data from the other location.
I just would like it so if there is nothing copied, it brings up a message box that says 'Copy data first!' and doesn't do anything else.
I've tried the following, but it still continues the code and messes up:
On Error GoTo Errormessage
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Exit Sub
Errormessage:
MsgBox "Copy data first!"
Here is the code without:
Sub Create()
If MsgBox("An Excel copy will be generated and you'll be notified to save.", vbYesNo) = vbNo Then Exit Sub
Application.ScreenUpdating = False
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
Application.ScreenUpdating = True
End Sub
Really appreciate any support on this.
Thank you.
Regards
Martin