Sub CreateInvoice()
Dim InvoiceNumber As Long, ipos As Long, tempstr As String, TempStr2 As String
Dim SID As String, Desc As String, Sbt, VAT, CurDat
' Create Invoice sheet; delete older sheets if they exist
If (Worksheets.Count > 1) Then
tempstr = InputBox("Other sheets already exist. Do you wish to overwrite: Y/N", vbYesNo)
If UCase(Left(tempstr, 1)) = "Y" Then
Worksheets("Invoice").Delete
Else
Exit Sub
End If
End If
ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
Sheets(4).Name = "Invoice"
With Worksheets("Invoice")
' get current invoice number
tempstr = "C:\keith\test-bell" ' ***ALTER THIS PATHNAME
If Dir(tempstr) = "" Then
InvoiceNumber = 999 ' ***SET THIS TO YOUR NEXT INVOICE# - 1, JUST THE 1ST TIME
Else
Open tempstr For Input As #1
On Error GoTo AbEnd1
While Not EOF(1)
Line Input #1, TempStr2
InvoiceNumber = Val(TempStr2)
Wend
AbEnd1:
Close #1
End If
' Increment the invoice file
InvoiceNumber = InvoiceNumber + 1
Open tempstr For Output As #1
Print #1, InvoiceNumber
Close #1
' Fill out the Invoice sheet
.Cells(12, 10) = UCase(Sheets(1).Cells(1, 2))
.Cells(14, 7) = InvoiceNumber
.Cells(14, 10) = Date
.Cells(21, 1) = Date
.Cells(7, 1) = Sheets(1).Cells(33, 1)
.Cells(8, 1) = Sheets(1).Cells(33, 2)
.Cells(9, 1) = Sheets(1).Cells(33, 4)
.Cells(10, 1) = Sheets(1).Cells(33, 6)
.Cells(11, 1) = Sheets(1).Cells(33, 8)
.Cells(21, 3) = Sheets(1).Cells(1, 4)
.Cells(21, 10) = Sheets(1).Cells(26, 3) - Sheets(1).Cells(21, 7)
.Cells(38, 6) = "CARRIAGE AND PACKING"
.Cells(38, 10) = Sheets(1).Cells(21, 7)
.Cells(40, 7) = "SUB TOTAL £"
.Range("J40").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-19]C:R[-2]C)"
.Cells(41, 7) = "VAT @ 20 % £"
.Range("J41").Select
ActiveCell.FormulaR1C1 = "=R[-1]C*0.20"
.Cells(44, 7) = "TOTAL £"
.Range("J44").Select
ActiveCell.FormulaR1C1 = "=R[-4]C+R[-3]C"
' format the invoice sheet
Worksheets("Invoice").Select
.Columns("A:J").Select
Selection.Font.Bold = True
.Range("C17").Select
Selection.NumberFormat = """Invoice Number: ""@"
.Range("J21:J44").Select
Selection.NumberFormat = """£""#,##0.00;[Red]-""£""#,##0.00"
.Range("J44").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
.Range("D17").Select
Selection.NumberFormat = "mmmm d, yyyy"
.Columns("J:J").Select
With Selection
.HorizontalAlignment = xlRight
End With
.Range("A1").Select
' do page setup here
Application.ScreenUpdating = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$44"
With ActiveSheet.PageSetup
.LeftMargin = 60 ' ADJUST TO METRIC UNITS IF NEEDED
.RightMargin = 0 ' ADJUST TO METRIC UNITS IF NEEDED
.TopMargin = 80 ' ADJUST TO METRIC UNITS IF NEEDED70
.BottomMargin = 27 ' ADJUST TO METRIC UNITS IF NEEDED
.HeaderMargin = 17 ' ADJUST TO METRIC UNITS IF NEEDED
.FooterMargin = 27 ' ADJUST TO METRIC UNITS IF NEEDED
.Orientation = xlPortrait
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
Application.ScreenUpdating = True
End With
End Sub