Sub fullmacsro()
FillCells
TodaysDate
PostToRegister
SavePdf
SaveXLMFile
NextInvoice
End Sub
Sub FillCells()
Dim Start As Boolean
Dim Rng1 As Range, Rng3 As Range, Rng4 As Range
Dim Prompt As String, RngStr As String
Dim Cell As Range
'set your ranges here
'Rng1 is on sheet "Group Profile" and cells B5 through B14
'Cell F1, A range of F5 through F7 etc. you can change these to
'suit your needs.
Set Rng1 = Sheets("1 Invoice Notes").Range("B4,B8,B9,B10,B14,B15,B17,B29,B30,B31,B32,D8,D9,F30,F31,J37")
'message is returned if there are blank cells
Prompt = "Please check your data ensuring all required " & _
"cells are complete." & vbCrLf & "you will not be able " & _
"to close or save the workbook until the form has been filled " & _
"out completely. " & vbCrLf & vbCrLf & _
"The following cells are incomplete and have been highlighted yellow:" _
& vbCrLf & vbCrLf
Start = True
'highlights the blank cells
For Each Cell In Rng1
If Cell.Value = vbNullString Then
Cell.Interior.ColorIndex = 6 '** color yellow
If Start Then RngStr = RngStr & Cell.Parent.Name & vbCrLf
Start = False
RngStr = RngStr & Cell.Address(False, False) & ", "
Else
Cell.Interior.ColorIndex = 0 '** no color
End If
Next
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
Start = True
If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
Start = True
If RngStr <> "" Then RngStr = RngStr & vbCrLf & vbCrLf
If RngStr <> "" Then RngStr = Left$(RngStr, Len(RngStr) - 2)
If RngStr <> "" Then
MsgBox Prompt & RngStr, vbCritical, "Incomplete Data"
Cancel = True
Else
'saves the changes before closing
ThisWorkbook.Save
Cancel = False
End If
Set Rng1 = Nothing
Set Rng3 = Nothing
Set Rng4 = Nothing
End Sub
Sub TodaysDate()
Range("B6").Value = Format(Now(), "MM/DD/YYYY")
End Sub
Sub PostToRegister()
Dim WS1 As Worksheet
Dim WS4 As Worksheet
Set WS1 = Worksheets("1 Invoice Notes")
Set WS4 = Worksheets("4 Register")
' Figure out which Row Is next
NextRow = WS4.Cells(Rows.Count, 1).End(xlUp).Row + 1
' Write the important Values To Register Last Number is How many items
WS4.Cells(NextRow, 1).Resize(1, 7).Resize(1, 7).Value = Array(WS1.Range("B6"), WS1.Range("D6"), WS1.Range("B8"), WS1.Range("J37"), WS1.Range("J36"), WS1.Range("i33"), WS1.Range("J38"))
End Sub
Sub SavePdf()
'This macro opens the SaveAs option with the defult file path "you have to set this file path below" coverts the whole sheet into .pdf file format
'And opens the .pdf to view <-- you can disable the view after covert option with lower code: OpenAfterPublish:=False
pdfName = Sheet2.Range("D6") & Sheet2.Range("B8") & Sheet2.Range("D8")
ChDir "C:\Users\Terrance\Dropbox\Marks Custom Drywall And Finishing\3 INVOICING DEPOSITS\4 outstanding invoice"
fileSaveName = Application.GetSaveAsFilename(pdfName, _
fileFilter:="PDF Files (*.pdf), *.pdf")
If fileSaveName <> False Then
Sheet2.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
fileSaveName _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=True
End If
MsgBox "File Saved to" & " " & fileSaveName
End Sub
Sub SaveXLMFile()
Dim NewFN As Variant
ActiveSheet.Copy
NewFN = "C:\Users\Terrance\Dropbox\Marks Custom Drywall And Finishing\3 INVOICING DEPOSITS\6 Invoice Copy\" & Range("D6").Value & Range("B8").Value & Range("D8").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, xlOpenXMLWorkbook
ActiveWorkbook.Close
End Sub
Sub NextInvoice()
Range("D6").Value = Range("D6").Value + 1
Range("B8:B15").ClearContents
Range("D8:J9").ClearContents
Range("B17:B20").ClearContents
Range("D12:E29").ClearContents
Range("F12:G12").ClearContents
Range("F15:G15").ClearContents
Range("F18:G18").ClearContents
Range("F21:G21").ClearContents
Range("F24:G24").ClearContents
Range("F27:G27").ClearContents
Range("B4:H4").ClearContents
Range("B29:B35").ClearContents
Range("J37").ClearContents
Range("F30").ClearContents
Range("F31").ClearContents
End Sub
Thank you in advance. Any Help would be appreciated
Bookmarks