I'm currently trying to modify a Visual Basic macro to only save spreadsheets in a workbook that have had populated cells.
The current macro just saves the entire 16 sheet workbook as a PDF yet a maximum of 9 of these sheets are sometimes left uncompleted, yet are still saved.
I would like the macro to automatically check if these sheets have been populated, once the 'SAVE' button is clicked and then proceed to only save the filled out (complete) sheets as a PDF.
I would massively appricate any help!
The code below is how the macro currently works when just saving the entire workbook. (There is an IF statement check before it is saved to PDF.)
Sub SaveAsPDF()
With ThisWorkbook.Sheets("COVERPage1PRINT")
If (Len(.Range("C24")) = 0) Then
MsgBox "Ensure Serial Number or Stamp number are filled."
Exit Sub
ElseIf (Len(.Range("H17")) = 0) Then
MsgBox "Ensure Serial Number or Stamp Number are filled."
Exit Sub
Else
ChDir _
"P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive"
fname = Sheets("COVERPage1PRINT").Range("H17")
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"P:\Cells\Spool & Sleeves Cell\Flow Plot Records\EFA\Saved EFA PDF Archive\" & fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End If
End With
End Sub