Invoice and Quote template & Database

Jnb99

Board Regular
Joined
Mar 29, 2016
Messages
84
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:

VBA Code:
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.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Forum statistics

Threads
1,175,530
Messages
5,897,952
Members
434,688
Latest member
vi28

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
Top