tr4mp3r
12-10-2023, 07:39 PM
Hi everyone,
I'm creating a new invoice system where I want a macro that lets me generate a new invoice number and then automatically records the data of the previous invoice in a different sheet.
Please have a look at the code and let me know where i went wrong, thank you so much in advance!
tr4mp3r
---------------------------------------------------
'create new invoice - works good
Sub CreateNewInvoice()
Dim invno As Long
invno = Range("B2")
Range("B3,B4,B5,B6,B8,A12:A249,B12:B249,C12:C249,D12:D249").ClearContents
MsgBox "You next invoice number is " & invno + 1
Range("B2") = invno + 1
Range("B3").Select
ThisWorkbook.Save
End Sub
'add the data to the record - have error
Sub RecordofInvoice()
Dim invno As Long
Dim Storename As Range
Dim Shipfee As Currency
Dim dt_issue As Date
Dim logistic As Range
Dim note As Range
Dim brand As Range
Dim productn As Range
Dim pc As Range
Dim box As Range
Dim nextrec As Range
Set invno = Sheet10.Range("B2")
Set Storename = Sheet10.Range("B3")
Set Shipfee = Sheet10.Range("B6")
Set dt_issue = Sheet10.Range("B4")
Set logistic = Sheet10.Range("B5")
Set note = Sheet10.Range("B8")
Set brand = Sheet10.Range("A12:A249")
Set productn = Sheet10.Range("B12:B249").SpecialCells(xlCellTypeConstants)
Set pc = Sheet10.Range("C12:C249")
Set nextrec = Sheet0.Range("T104876").End(xlUp).Offset(1, 0)
invno.Copy nextrec
Dim DestCell As Range
If Sheet4.Range("I4") = "" Then 'If A2 is empty
Set DestCell = Sheet4.Range("I4") '...then destination cell is A2
Else
Set DestCell = Sheet4.Range("I3").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If
invno.Copy DestCell.Offset(0, -3)
dt_issue.Copy DestCell.Offset(0, -2)
Storename.Copy DestCell.Offset(0, -1)
productn.Copy DestCell
pc.Copy DestCell.Offset(0, 1)
box.Copy DestCell.Offset(0, 2)
Shipfee.Copy DestCell.Offset(0, 3)
logistic.Copy DestCell.Offset(0, 4)
note.Copy DestCell.Offset(0, 5)
End Sub
I'm creating a new invoice system where I want a macro that lets me generate a new invoice number and then automatically records the data of the previous invoice in a different sheet.
Please have a look at the code and let me know where i went wrong, thank you so much in advance!
tr4mp3r
---------------------------------------------------
'create new invoice - works good
Sub CreateNewInvoice()
Dim invno As Long
invno = Range("B2")
Range("B3,B4,B5,B6,B8,A12:A249,B12:B249,C12:C249,D12:D249").ClearContents
MsgBox "You next invoice number is " & invno + 1
Range("B2") = invno + 1
Range("B3").Select
ThisWorkbook.Save
End Sub
'add the data to the record - have error
Sub RecordofInvoice()
Dim invno As Long
Dim Storename As Range
Dim Shipfee As Currency
Dim dt_issue As Date
Dim logistic As Range
Dim note As Range
Dim brand As Range
Dim productn As Range
Dim pc As Range
Dim box As Range
Dim nextrec As Range
Set invno = Sheet10.Range("B2")
Set Storename = Sheet10.Range("B3")
Set Shipfee = Sheet10.Range("B6")
Set dt_issue = Sheet10.Range("B4")
Set logistic = Sheet10.Range("B5")
Set note = Sheet10.Range("B8")
Set brand = Sheet10.Range("A12:A249")
Set productn = Sheet10.Range("B12:B249").SpecialCells(xlCellTypeConstants)
Set pc = Sheet10.Range("C12:C249")
Set nextrec = Sheet0.Range("T104876").End(xlUp).Offset(1, 0)
invno.Copy nextrec
Dim DestCell As Range
If Sheet4.Range("I4") = "" Then 'If A2 is empty
Set DestCell = Sheet4.Range("I4") '...then destination cell is A2
Else
Set DestCell = Sheet4.Range("I3").End(xlDown).Offset(1, 0) '...otherwise the next empty row
End If
invno.Copy DestCell.Offset(0, -3)
dt_issue.Copy DestCell.Offset(0, -2)
Storename.Copy DestCell.Offset(0, -1)
productn.Copy DestCell
pc.Copy DestCell.Offset(0, 1)
box.Copy DestCell.Offset(0, 2)
Shipfee.Copy DestCell.Offset(0, 3)
logistic.Copy DestCell.Offset(0, 4)
note.Copy DestCell.Offset(0, 5)
End Sub