Invoicing Summary - Taking certain information from an invoice to add to another workbook

[domgee]

Hi All,

I am in the process of creating an invoicing program for work. Essentially I would like to capture some items of an invoice that can be collected on another worksheet (Let's Call it 'Reporting Page') to create an organized report. I would like to do this by creating a button

I would need to program the button so that it can capture and record the following on the 'Reporting Page':
- Invoice Number
- Date
- Customer
- Quantities for the certain items that are purchased.

What I would also need to have done is that with every new invoice I create that the information can be properly recorded in the next column on the 'Reporting Page'.

Please help. I am new in this forum but have seen how helpful this website truly is.

[krjoshi]

Hi domgee,
WELCOME TO THE FORUM!

Please upload a sample workbook with some data. e.g show two seperate sheets one for before the Marco run then one for After the Marco run.

[BigBas]

Do you have an invoice template developed? Have you gotten anywhere with creating the formatting of the workbook?

If so, upload a copy so that we all can see what we are working with.

[domgee]

Here is the worksheet I'm working off of. I have set up the template how I would like to track the info on two worksheets but I have not applied a Macro to the button.

Thanks for all the help you.

[domgee]

Any luck with this problem? Did you need any more information?

[BigBas]

Hi Domgee,

Try using the following code and let me know if it has the desired result>

Sub SendInvoiceDataToReport()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rngINV As Range
    Dim rngRPT As Range
    Dim cel As Range
    Dim nCol As Long
    
    Application.ScreenUpdating = False
    
    Set ws1 = Sheets("Invoice")
    Set ws2 = Sheets("Reporting Sheet")
    Set rngINV = ws1.Range("C12:C67")
    Set rngRPT = Range(ws2.Range("a9"), ws2.Range("a9").End(xlDown))
    nCol = ws2.Cells(3, Columns.Count).End(xlToLeft).Column + 1
        
    If WorksheetFunction.CountA(rngINV) = 0 Then
        MsgBox "No items listed"
        Exit Sub
    ElseIf WorksheetFunction.CountA(rngINV) = 1 Then
        Set rngINV = Range("C12")
    Else
        Set rngINV = Range(rngINV.Range("A1"), rngINV.Range("A1").End(xlDown))
    End If
    
    ws2.Cells(3, nCol) = ws1.Cells(7, 4)
    ws2.Cells(4, nCol) = ws1.Cells(7, 6)
    ws2.Cells(5, nCol) = ws1.Cells(1, 6)
    ws2.Cells(6, nCol) = ws1.Cells(70, 6)
    
    For Each cel In rngINV
        rngRPT.Find(cel).Offset(, nCol - 1) = cel.Offset(, 1)
    Next cel
        
    Application.ScreenUpdating = True
End Sub

[Tinbendr]

The same thing only different.

Sub Add2Report()
Dim WS As Worksheet
Dim A As Long
Dim LastRow As Long
Dim NextCol As Long

Set WS = ActiveWorkbook.Worksheets("Reporting Sheet")

With WS
    LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    NextCol = WS.Cells(9, .Columns.Count).End(xlToLeft).Column + 1
    
    'Customer
    .Cells(3, NextCol).Formula = "=Invoice!$B7"
    'Invoice #
    .Cells(4, NextCol).Formula = "=Invoice!$F7"
    'Date
    .Cells(5, NextCol).Formula = "=Invoice!$F1"
    'Total
    .Cells(6, NextCol).Formula = "=Invoice!$F70"
    
    '=VLOOKUP(A9,Invoice!C12:D66,2,0)
    For A = 9 To LastRow
        .Cells(A, NextCol).Formula = "=VLOOKUP(A" & A & ", INVOICE!$C$12:$D$66,2,0)"
    Next
End With
End Sub