Thanks for your quick reply
I tried this code before but system bypass the macro and saving the file without any values in the cell.
I write the below code as per your input.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If IsEmpty(Range("I5:M5").Value) Then
MsgBox "Fill value in A1"
Cancel = True
Else
SaveAsPDF
End If
End Sub
Module code is as under
Sub VariationNumber()
'To Gereate the Next Serial number of any Documents
Range("D2").Value = Range("D2").Value + 1
' To Clear the Contents of the Specified Cells
Range("A8:B19").ClearContents
Range("F8:M19").ClearContents
Range("C21:E21").ClearContents
Range("I21:M21").ClearContents
Range("I5:M5").ClearContents
End Sub
Sub SaveWithNewName()
Dim NewFn As Variant
PostToRegister
ActiveWorkbook.Save
ActiveSheet.Copy
NewFn = "C:\Users\19973\Desktop\Variation Reports\Variation" & Range("D2").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFn, xlOpenXMLWorkbook
ActiveWorkbook.Close True
VariationNumber
Save
End Sub
Sub PostToRegister()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("VariationReport")
Set WS2 = Worksheets("VariationRegister")
'Figure Out which row is the next row
NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
'Write the important Value to Register
WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("Date"), WS1.Range("Vno"), WS1.Range("ShipperID"), WS1.Range("StoreKeeperName"))
End Sub
Sub PrintVar()
'To Print the Active Sheet
ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
Sub Save()
'To Save an active Sheet
ActiveWorkbook.Save
ActiveWorkbook.SaveCopyAs Filename:=ActiveWorkbook.Name
End Sub
Sub SaveAsPDF()
'To Save a File as PDF in Other Folder
PostToRegister
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Users\19973\Desktop\Variation Reports\Variation" & Range("D2").Value & ".pdf"
VariationNumber
Save
End Sub