Save Invoice Details to another sheet each time the save button is clicked

[Oteez]

Hi,
Please help me Out. I am trying to save entries done on an invoice in excel into same workbook but another sheet.

Sub Invoice_SaveUpdate()
Dim InvRow As Long, InvItemRow As Long, LastItemRow As Long, ItemRow As Long
With Sheet1
    'Determine New or Existing Invoice
    If .Range("B3").Value = Empty Then 'New Invoice
        InvRow = Sheet4.Range("A99999").End(xlUp).Row + 1  'First Available Row
        .Range("L3").Value = .Range("N3").Value 'Add Invoice Number
        Sheet4.Range("A" & InvRow).Value = .Range("L3").Value 'Add Invoice #
    Else: 'Existing Invoice
        InvRow = .Range("B3").Value 'Existing Invoice Row
    End If
    Sheet4.Range("B" & InvRow).Value = .Range("L2").Value 'Date
    Sheet4.Range("C" & InvRow).Value = .Range("I5").Value 'Customer Name
    Sheet4.Range("D" & InvRow).Value = .Range("L23").Value 'Invoice Total
    Sheet4.Columns("A:D").AutoFit
       
'Add / Update Invoice Items
LastItemRow = Sheet1.Range("E20").End(xlUp).Row  'Last Invoice Item Row
If LastItemRow < 11 Then GoTo NoItems
    For ItemRow = 11 To LastItemRow
        If .Range("N" & ItemRow).Value <> Empty Then 'existing Row
            InvItemRow = .Range("N" & ItemRow).Value
        Else 'New Row
             InvItemRow = Sheet3.Range("A9999").End(xlUp).Row + 1   'First AvailRow
             Sheet3.Range("A" & InvRow).Value = .Range("L3").Value  'Invoice #
             Sheet3.Range("B" & InvRow).Value = .Range("L2").Value  'Invoice Date
             Sheet3.Range("C" & InvRow).Value = .Range("I5").Value  'Vendor
             Sheet1.Range("N" & ItemRow).Value = InvItemRow 'Invoice Item Row
             Sheet3.Range("M" & InvItemRow).Value = "=Row()" 'Add Row #
        End If
        Sheet3.Range("D" & InvItemRow & ":K" & InvItemRow).Value = .Range("E" & ItemRow & ":L" & ItemRow).Value 'Add Desc, Qty, Price
        Sheet3.Range("L" & InvItemRow).Value = ItemRow
        Sheet3.Columns("A:AC").AutoFit
    Next ItemRow
NoItems:
.Range("B4").Value = False 'Set new invoice to false
.Shapes("CANCEL").Visible = msoFalse
.Shapes("NEW").Visible = msoCTrue
End With
End Sub

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

Are you saying that you want to run that code any time the workbook is saved?

• If so, put this in the ThisWorkbook module in the vba window.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  Invoice_SaveUpdate
End Sub

• If not, please provide more details.