Creating an Invoice from data on an excel spr

June 2, 2009 at 11:16:56
Specs: Windows XP
I have a pricing tool with 200 rows on an excel spreadsheet. If a customer orders 2 items from the 200 I would like the quantity I enter and the cooresponding prices to populate over to an invoice that I have created on another spreadsheet. How do I do this where the items ordered show up in sequential rows on the invoice since the items ordered are not listed one right after the other on the pricing tool spreadsheet?

See More: Creating an Invoice from data on an excel spr

Report •


#1
June 2, 2009 at 12:06:18
I would try a Worksheet_Change macro.

Without knowing the exact layout of both your Pricing Tool and Invoice sheets, all I can offer is the concept. You will need to modify this to fit your own needs.

Assumptions:

- Your Invoice sheet is named "Invoice"
- You are entering your Quantity in Column C of the Pricing Tool sheet
- You want to copy the entire row from the Pricing Tool to the Invoice sheet.

Note: I have no expectations that these assumptions are anything close to what you are doing. I'm simply showing you one method of copying non-contiguous data from one sheet to contiguous rows on another.

By clicking on the sheet tab for your Pricing Tool, clicking View Code and pasting this code in, it will run whenever you make a change to a cell in Column C of the Pricing Tool sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if a change is made to Quantity column (C)
 If Target.Column = 3 Then
'Determine next empty row in Invoice sheet
  nxtInvRow = Sheets("Invoice").Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy entire row from Pricing Tool sheet to Invoice sheet
  Target.EntireRow.Copy Destination:=Sheets("Invoice").Range("A" & nxtInvRow)
 End If
End Sub


Report •

#2
June 3, 2009 at 13:36:59
Thank you for this explination. I understand what to do, however, since the assumptions are different then what I need I am not sure how to re-write the code.

My assumptions are:
-My invoice sheet is named Invoice
-I am entering my quantity in Row J
-I want to copy over from the Pricing tool to the invoice sheet the following columns in this order: J, A, B, H (so not the entire row)
-On my invoice the first cell that will be populated into is C21

Can you re-write the code for me? Thanks so much!


Report •

#3
June 3, 2009 at 16:14:47
re: -I am entering my quantity in Row J

J is not a row

re: -I want to copy over from the Pricing tool to the invoice sheet the following columns in this order: J, A, B, H (so not the entire row)

I really don't think you want to copy "columns J, A, B, H"

Please clarify.


Report •

Related Solutions

#4
June 3, 2009 at 17:31:15
Sorry I meant to say J column. I want to enter what is in cells J2, A2, B2, H2 and so forth through J229, A229, B229, H229, but I only want to popluate these over IF a value greater than 0 is entered in Column J.

The way my invoice spreadsheet is set up, the first value will popluate in C21 which is the first row under my column heading quantity (which is what is in the J column in the pricing tool) then D21 is = what is in A column then E21 =what is in B column and F21 = what is in H column


Report •

#5
June 4, 2009 at 06:56:22
Assumption:

C21 is empty each time a new invoice is created. The code checks column C, starting in Row 21 to find the next empty cell.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if a value is entered in Quantity column (J) and that it is > 0
 If Target.Column = 10 Then
   If Target > 0 Then
'Determine next empty row in Invoice sheet
  nxtInvRow = 21
chkInvRow:
    If Sheets("Invoice").Cells(nxtInvRow, "C") <> "" Then
     nxtInvRow = nxtInvRow + 1
     GoTo chkInvRow
    End If
'Copy data from Pricing Tool sheet to Invoice sheet
  Cells(Target.Row, "J").Copy Destination:=Sheets("Invoice").Range("C" & nxtInvRow)
  Cells(Target.Row, "A").Copy Destination:=Sheets("Invoice").Range("D" & nxtInvRow)
  Cells(Target.Row, "B").Copy Destination:=Sheets("Invoice").Range("E" & nxtInvRow)
  Cells(Target.Row, "H").Copy Destination:=Sheets("Invoice").Range("F" & nxtInvRow)
   End If
 End If
End Sub


Report •

#6
June 4, 2009 at 13:19:53
WOW!!!! this code worked almost perfectly, you are AMAZING!!!

I say almost perfect because I failed to mention that on my Pricing Tool in column H all of the cells have a formula that generates the value in those cells (the formula is based on columns that I am not populating over to the Invoice).

So, like when you do paste special then click value is there a way to update the code to populate only the value from the Cells in column H of the pricing tool to Column K in the Invoice?

Also, in several cells in column J I have this formula inserted =IF($J$172>0,$J$172*1,0) (the column is always the same in this formula but the row number is different depending on which product it is) I did this formula since many of our products are connected (i.e if you buy 1 of Product A then you have to buy 1 of Product B and this formula automaticlly puts in to Product B's quantity cell whatever is entered into Product A's quantity cell). The code you wrote does not populate over Product B's information because of this formula, can you put something into the code to do this? If not, I can just take this formula out.


Report •

#7
June 4, 2009 at 13:34:06
Replace the .Copy lines with this if all you want is the values:

     Sheets("Invoice").Range("C" & nxtInvRow) = Cells(Target.Row, "J")
     Sheets("Invoice").Range("D" & nxtInvRow) = Cells(Target.Row, "A")
     Sheets("Invoice").Range("E" & nxtInvRow) = Cells(Target.Row, "B")
     Sheets("Invoice").Range("F" & nxtInvRow) = Cells(Target.Row, "H")

The part where you have cells automatically changing because of formulae is a little more difficult since the WorkSheet_Change macro only fires on a manual change, not when a cell is changed by a formula.

I'm going to have to think about that one.


Report •

#8
June 5, 2009 at 06:25:37
Try this code.

It includes a section to copy the data from the rows where Column J contains formulae that are Dependent on the Target cell. If there are no Dependent cells the Error Handling routine will skip that section of code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Check if a value is entered in Quantity column (J) and that it is > 0
 If Target.Column = 10 Then
   If Target > 0 Then
'Determine next empty row in Invoice sheet
  nxtInvRow = 21
chkInvRow:
    If Sheets("Invoice").Cells(nxtInvRow, "C") <> "" Then
     nxtInvRow = nxtInvRow + 1
     GoTo chkInvRow
    End If
'Copy data from changed row to Invoice Sheet
     Sheets("Invoice").Range("C" & nxtInvRow) = Cells(Target.Row, "J")
     Sheets("Invoice").Range("D" & nxtInvRow) = Cells(Target.Row, "A")
     Sheets("Invoice").Range("E" & nxtInvRow) = Cells(Target.Row, "B")
     Sheets("Invoice").Range("F" & nxtInvRow) = Cells(Target.Row, "H")
'Copy data from Dependent Cells (if any) to Invoice Sheet
      On Error GoTo NoDependents
       For Each depCell In Range(Target.Dependents.Address)
        nxtInvRow = nxtInvRow + 1
         Sheets("Invoice").Range("C" & nxtInvRow) = Cells(depCell.Row, "J")
         Sheets("Invoice").Range("D" & nxtInvRow) = Cells(depCell.Row, "A")
         Sheets("Invoice").Range("E" & nxtInvRow) = Cells(depCell.Row, "B")
         Sheets("Invoice").Range("F" & nxtInvRow) = Cells(depCell.Row, "H")
       Next
NoDependents:
   End If
 End If
End Sub


Report •

#9
June 9, 2009 at 13:42:48
Thanks so much for this! You have been so great answering all of my questions and I appreciate that so much. I do have a couple more questions though:

1) This new code works, but it is copying each row 4 times into the invoice sheet. Can you fix that?

2) I noticed that after entering a quantity into column J of the pricing tool when we delete out what we just entered it does not get deleted form the invoice sheet. Is there a way to incorporate that function into this code?

3) Finally, when I have several sheets in 1 workbook, but I only want to email 1 of those sheets, what is the best way to do that? Copy/Past the sheet into a new excel workbook? For example, I have the Pricing Tool as sheet 1 and the invoice as sheet 2, but I only want to email the customer the Invoice (sheet 2) how do I do that?


Report •

#10
June 9, 2009 at 14:47:11
re: it is copying each row 4 times into the invoice

That's doesn't happen to me, but obviously I can only set up my workbook based on the description you've provided. There must be something different in your workbook that causes that. I've sent you a email address via PM. If you want to send me a copy of the workbook, I'll take a look at it, but I can't let this turn into a major project.

re: when we delete out what we just entered it does not get deleted form the invoice

Maybe that's because you never asked for that.

Once I see the workbook, I can see if that's possible. I need to know something first. Are you asking to start with a fresh invoice each time (fairly easy) or are you asking to just delete those items that you zero out, leaving the others on the invoice? (That's much harder)

re: I only want to email the customer the Invoice

DAGS

Here's just one of many hits.

http://excel.tips.net/Pages/T003273...


Report •


Ask Question