Invoice and Quote template & Database

[Jnb99]

Hi everyone,

I posted a while ago about a workbook I am struggling with. Luckily didn't get any response, because the entire setup need to change.

I need help please!!

Currently estimate sheet is saved as Pdf and excel sheet in a folder. There was a clear contents function as well which I took out because it was clearing the wrong "active workbook" if that makes sense. Code used for that were:

Private Sub CommandButton1_Click()
   
    Dim saveLocation As String
    Dim rng As Range
    saveLocation = "C:\Users\***\Estimates\" & Range("f4").Value & Range("a14").Value & ".pdf"
   
    Set rng = Worksheets("Estimate").Range("A1:g50")
    rng.ExportAsFixedFormat Type:=xlTypePDF, Filename:=saveLocation
   
    Call saveSheetWithoutFormulas
   
End Sub

Sub saveSheetWithoutFormulas()
   Dim saveLocation As String, xlsxFile  As String
    Dim WB As Workbook, WS As Worksheet
    Dim SheetName As String
   
    SheetName = ActiveSheet.Name
   
    saveLocation = "C:\Users\***\Estimates\" & ActiveSheet.Range("f4").Value & Range("a14").Value
    xlsxFile = Split(saveLocation, ".")(0) & ".xlsx"
   
    Application.DisplayAlerts = False
    ThisWorkbook.SaveCopyAs Filename:=saveLocation
   
    Set WB = Application.Workbooks.Open(Filename:=saveLocation)
    Set WS = WB.Worksheets(SheetName)
    WS.UsedRange.Value = WS.UsedRange.Value

    WB.saveas Filename:=xlsxFile, FileFormat:=xlOpenXMLWorkbook    'see XlFileFormat Enumeration for different formats
    WB.Close False
    Application.DisplayAlerts = False
    Kill saveLocation
   
End Sub

The new template must be layout as follow:
There are 4 sheet applicable to the vba:
1. Invoice template
2. estimate template
3. Invoice database
4. Estimate database

Both Invoice and estimate templates look exactly the say in terms of cell layout.
Date: F3
Document number: F4
Client: A14
Document total: G44 (I assume I must rename this cell as "DOCUMENT TOTAL" or something like that, in case rows are added to the document.)

Quote template:
Button 1: Save estimate and clear
This button should save the estimate sheet as a new sheet in the same workbook, with the new sheet name as the document number in F4. When the document is saved, I would like it to add the document details to a database i.e. estimate database, with only the above mentioned (F3, F4, A14, DOCUMENT TOTAL) cells info transferred. When the document is saved to the database, document number must +1.

Quote: (This is the actual new sheet saved from the quote template, with document number as the sheet name)
I am not sure if this will be possible, but its worth a try. I would like to put a button on the newly created estimate sheet, named "CONVERT TO SALE"
This button must look for the next available invoice number from the invoice database, and save it as a new sheet, same as above quote. When invoice is saved, document number on invoice template +1.

I know this is a tall order, I hope someone can help.