VBA Exit Sub

george hart

Board Regular
Joined
Dec 4, 2008
Messages
241
Help...

The code below works fine, yet when the condition If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
MsgBox "You must enter the responsible owner for all shortforms prior exporting." Is true I dont want the following code to run. I want it to exit sub. If it's not true then I want it to run. Any ideas???????

Dim NewFileName
Dim X As Variant
For X = 6 To 33
Application.DisplayAlerts = False
Application.ScreenUpdating = False
If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
MsgBox "You must enter the responsible owner for all shortforms prior exporting."
Next
Sheets("All Trains").Select
Sheets("All Trains").Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Rows("47:54").Select
Selection.Delete Shift:=xlUp
Range("B1").Select

NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _
Format$(Range("C3").Value, "ddmmmyy") & ".xls"
For Each ws In Worksheets
ActiveSheet.Name = Format(Range("C3").Value, "yymmdd")
Next
ActiveWorkbook.SaveAs Filename:=NewFileName
ActiveWorkbook.Close

MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3")

Application.DisplayAlerts = True
Application.ScreenUpdating = True


Many thanks in advance...
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Code:
Dim NewFileName
Dim X As Variant
For X = 6 To 33
If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" [B]Then [/B]
MsgBox "You must enter the responsible owner for all shortforms prior exporting."
[B]Exit Sub
End if[/B]
Next
[B]Application.DisplayAlerts = False
Application.ScreenUpdating = False[/B]
Sheets("All Trains").Select
    Sheets("All Trains").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Rows("47:54").Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select
 
     NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _
    Format$(Range("C3").Value, "ddmmmyy") & ".xls"
    For Each ws In Worksheets
ActiveSheet.Name = Format(Range("C3").Value, "yymmdd")
Next
    ActiveWorkbook.SaveAs Filename:=NewFileName
    ActiveWorkbook.Close
    
    MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3")
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
 
Last edited:
Upvote 0
Try changing
Code:
If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
MsgBox "You must enter the responsible owner for all shortforms prior exporting."
to
Code:
If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then
MsgBox "You must enter the responsible owner for all shortforms prior exporting."
Exit Sub
End If
 
Upvote 0
Many thanks The code below worked a treat....Thanks again...Dim NewFileNameDim X As VariantFor X = 6 To 33If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then MsgBox "You must enter the responsible owner for all shortforms prior exporting."Exit SubEnd ifNextApplication.DisplayAlerts = FalseApplication.ScreenUpdating = FalseSheets("All Trains").Select Sheets("All Trains").Copy Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Rows("47:54").Select Selection.Delete Shift:=xlUp Range("B1").Select NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _ Format$(Range("C3").Value, "ddmmmyy") & ".xls" For Each ws In WorksheetsActiveSheet.Name = Format(Range("C3").Value, "yymmdd")Next ActiveWorkbook.SaveAs Filename:=NewFileName ActiveWorkbook.Close MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3") Application.DisplayAlerts = True Application.ScreenUpdating = True
 
Upvote 0

Forum statistics

Threads
1,213,582
Messages
6,114,472
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top