1. 3 sheets

• invoice
• recofinv
• inctype
  in the record of invoice i am writing a header, inv# from cell h3,inv date = cell h4, customer =”cell C8 and total amount of inv I24, with other information from a cell in invoice called category.

2. i also want to write the following details to another sheet called “inctype”.

• invnr = h3 date = h4 customer = c8 code = b14 qty=h14 itemamt=i14
• each line entry for line 14 to 22 each on its own line
  the problem with this is it writes only line 14 and ignore lines 15-22 in the inctype sheet

Here is my code
Sub CreateNewInvoiceAndEmail()
Dim invno As Long
Dim wsInvoice As Worksheet
Dim wsRecordOfInvoices As Worksheet
Dim wsInctype As Worksheet
Dim lastRowInvoice As Long
Dim i As Long
Dim category As String
Dim amount As Double
Dim categoryColumn As Long
Dim existingRow As Range
Dim nextRow As Long
Dim existingAmount As Double
Dim path As String
Dim fname As String
Dim incTypeRow As Long ‘ Counter for rows in inctype sheet
Dim invRow As Long ‘ Counter for rows in Invoice sheet

' Set references to the Invoice, Record of Invoices, and Inctype sheets
Set wsInvoice = ThisWorkbook.Sheets("Invoice") ' Update with your actual sheet name
Set wsRecordOfInvoices = ThisWorkbook.Sheets("RecOfInv") ' Update with your actual sheet name
Set wsInctype = ThisWorkbook.Sheets("inctype") ' Update with your actual sheet name

invno = wsInvoice.Range("H3").Value
lastRowInvoice = wsInvoice.Cells(wsInvoice.Rows.Count, "F").End(xlUp).Row

' Determine the next available row in Inctype sheet
incTypeRow = wsInctype.Cells(wsInctype.Rows.Count, "A").End(xlUp).Row + 1

' Loop through each row in Invoice sheet to update Record of Invoices and Inctype
For invRow = 14 To lastRowInvoice ' Rows 14 to the last used row in Invoice sheet
    category = wsInvoice.Cells(invRow, "F").Value
    amount = wsInvoice.Cells(invRow, "I").Value
    
    Debug.Print "Category to match: " & category
    
    ' Find the column number in Record of Invoices for the category
    On Error Resume Next
    categoryColumn = WorksheetFunction.Match(category, wsRecordOfInvoices.Rows(1), 0)
    On Error GoTo 0
    
    Debug.Print "Category column: " & categoryColumn
    
    If categoryColumn > 0 Then
        ' Check if there's already an entry for this category in Record of Invoices
        Set existingRow = wsRecordOfInvoices.Columns("A").Find(What:=invno, LookIn:=xlValues, LookAt:=xlWhole)
        
        If existingRow Is Nothing Then
            ' Write the invoice details to Record of Invoices
            nextRow = wsRecordOfInvoices.Cells(wsRecordOfInvoices.Rows.Count, "A").End(xlUp).Row + 1
            wsRecordOfInvoices.Cells(nextRow, "A").Value = invno
            wsRecordOfInvoices.Cells(nextRow, "B").Value = wsInvoice.Range("C8").Value ' Customer Name from C8 in Invoice sheet
            wsRecordOfInvoices.Cells(nextRow, categoryColumn).Value = amount ' Write amount to category column
            wsRecordOfInvoices.Cells(nextRow, "C").Value = wsInvoice.Range("H24").Value ' Total Amount
            wsRecordOfInvoices.Cells(nextRow, "D").Value = wsInvoice.Range("H4").Value ' Date Issued
            wsRecordOfInvoices.Cells(nextRow, "E").Value = wsInvoice.Range("H4").Value + wsInvoice.Range("H5").Value ' Date Due
            
            ' Save the invoice as PDF
            path = "G:My DriveNampolFinanceInvoices 2024"
            fname = invno & " - " & wsInvoice.Range("C8").Value & ".pdf"
            wsInvoice.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=False, Filename:=path & fname
            ' Save the PDF path as hyperlink in column 7 of Record of Invoices
            wsRecordOfInvoices.Cells(nextRow, "G").Hyperlinks.Add Anchor:=wsRecordOfInvoices.Cells(nextRow, "G"), Address:=path & fname, TextToDisplay:=fname
            
            ' Insert data into Inctype sheet
            
            wsInctype.Cells(incTypeRow, "A").Value = invno
            wsInctype.Cells(incTypeRow, "B").Value = wsInvoice.Range("H4").Offset(invRow - 14, 0).Value ' Date from H14, H15, ...
            wsInctype.Cells(incTypeRow, "C").Value = wsInvoice.Range("C8").Value ' Customer Name
            wsInctype.Cells(incTypeRow, "D").Value = wsInvoice.Cells(invRow, "B").Value ' Code from B14, B15, ...
            wsInctype.Cells(incTypeRow, "E").Value = wsInvoice.Cells(invRow, "I").Value ' Item Amount from I14, I15, ...
            wsInctype.Cells(incTypeRow, "F").Value = wsInvoice.Cells(invRow, "H").Value ' Qty from H14, H15, ...
            
            ' Increment incTypeRow for the next row in Inctype sheet
            incTypeRow = incTypeRow + 1
        Else
            ' If an entry already exists, update the amount for the category
            existingAmount = existingRow.Offset(0, categoryColumn - 1).Value ' Offset to the category column
            
            ' Sum existing amount with new amount for the category
            existingRow.Offset(0, categoryColumn - 1).Value = existingAmount + amount
        End If
    Else
        ' Handle case where category is not found
        MsgBox "Category '" & category & "' not found in Record of Invoices."
    End If
Next invRow

' Send email with PDF attachment
' Code for sending email goes here...

MsgBox "Next Invoice # is " & invno + 1

' Update the invoice number for the next invoice
wsInvoice.Range("H3").Value = invno + 1

' Clear specific ranges in the Invoice sheet
Range("C8:F8,B14:B22,F14:G22,H14:H22").ClearContents

ThisWorkbook.Save

End Sub