Transfer data into Invoice

I have my listing as quantity, description, price (columns A, B, C) on sheet 1. My invoice on sheet 2 matches that in order and column.

I want it to be like this...I enter 1 in the quantity column on sheet 1. The software copies the quantity, description and price and puts it on my invoice. So when I'm done if I used 5 different items from sheet 1 (out of probably 100), the 5 items I entered a quantity for will be populated (accordingly) on my invoice.

Hi Semperfi,

To make this work I assumed that column A on the invoice sheet (Sheet2) is empty, except for any headers you might have.

You didn't specify if you wanted the quantities to reset, so check the green text in the code.

Implement the following code by right-clicking sheet1's tab, selecting View code and pasting the code in the big white field of the newly opened window:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target = vbNullString Then Exit Sub
If IsNumeric(Target) = False Then Exit Sub

Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial

'If you want the quantity to reset, then remove the apostrophe at the beginning of the below sentence
'Target.ClearContents

Application.CutCopyMode = False

End Sub

Best regards,
Trowa

Thanks lot TrowaD this got me on the right track. The only thing I haven't remedied is this. The code you supplied me gives me everything I need on Sheet 2, but it puts it at the bottom of my invoice. My informal table I'd like it copied into is from A18:C34. Is there a way I can make it go to that location and populate down? I modified your code to the following:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target = vbNullString Then Exit Sub
If IsNumeric(Target) = False Then Exit Sub

Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy
Sheets("Sheet2").Range("A18").PasteSpecial

Application.CutCopyMode = False

End Sub


When I do that I get my data starting in A18 like I want, but when I use another "piece of equipment" from Sheet 1, it pastes over top of A18. Can I alter this just a tad to have them go down the column?

Hi Semperfi,

That depends on where your other data is located.

If A1:A17 is filled with data you can use:

Sheets("Sheet2").Range("A1").End(xldown).offset(1,0).PasteSpecial

If A17 is filled with data then you can use:

If Sheets("Sheet2").Range("A18") = vbNullString Then
    Sheets("Sheet2").Range("A18").PasteSpecial
    Else: Sheets("Sheet2").Range("A17").End(xlDown).Offset(1, 0).PasteSpecial
End If

Also note that you can use End multiple times.
So if you have data in A17 and A35 then the following will also work:

Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).End(xlUp).Offset(1, 0).PasteSpecial

If none of these work then let me now which cells in column A contains data.

Best regards,
Trowa

Is there a way I can either upload my excel sheet to this forum or send you my excel sheet? The only reason I ask is you seem to understand what you're doing very thoroughly, but I'm not able to explain exactly what I have already established in my excel sheet.