populate invoice-sheet1 with info from sheet2

Hi,Ive very minimally modified the below code based on another response i found on here. The basics are there for what i need but basically i want if the quantity in column a is changed to zero that the entry onto sheet 1 is removed, and if a quantity in column a is changed from 1 to 2 or whatever, that it changes the entry on sheet 1 to match, rather than adding another row with the new quantity as well as retaining the old row.So basically on sheet 1, if i have columns a, b, c & d, with quantity, description, size, & choice from data validation list (either option 1 or 2).I also want each entry on the invoice to end up on the next row, which could be quite difficult maybe if you go back and change something that was previously 1 or higher, to a zero.But i have absolutely no idea how to code aside from very basic macro stuff.Any help would be much appreciated!

We need some clarification about what you have and what you want to do.

1. What is the name of the worksheet containing the source "invoice" data. Is it "Sheet2"2. Assuming that the invoice is a worksheet, does it have one set of the information:quantity, description, size, & choice,or more than one set. The macro code you posted suggests that the 'invoice' data is on the same worksheet as the stored information from all the invoices, but you subject line suggests that it is "Sheet2"3. What cells on the invoice worksheet (Sheet2?) contain the four items (quantity, description, size, & choice) that you are inputting or changing.4. If the trigger for updating the stored data is a change in the quantity in column A, what happens if you change the quantity first and then change one of the other items - the macro appears to only respond to a change in column A. If for example you change the quantity and the stored information is updated, you then change the size and description values in the invoice, the stored data will not be updated to match. Do you want the macro to respond to changes in any of the four items.5. Can you confirm that your data is stored on "sheet1" (not on "Sheet1" or "sheet 1")6. As you want the stored information updated, it is necessary that the invoice has a unique identifier that enables the code to find it in the list of stored data. Where is the unique ID - you have only mentioned four columns - quantity, description, size, & choice.7. The macro you posted includes code to copy dependents of the Target cell - in other word if the value of the 'invoice cell' in column A changes, your stored invoice data is not updated with the changed invoice data but with a value from a cell dependent on the Target cell. If cell M10 has a formula =A4*2 then when the 'invoice cell' A4 is changed from 2 to 3, instead of 3 being stored, the value in M10 is stored, i.e. 6. Is this what you want to happen. 7. I know that you took this code from somewhere else, but are you sure it does what you want.

Will answer as best as i can.1) The name of the sheet containing invoice data is called 'invoice'. The name of the sheet containing the data is called 'costs'. The code was pasted under general for the costs tab. ie. when you right click on the costs sheet and click view code, it is pasted in there.2)Basic run down of what cost sheet looks like & does. It calculates printing costs. But i only want quantity/description/size/choice to across to each row of the invoice, not cost. That will be referenced seperately at the bottom. The costs sheet has a row for each item. For examplerow 1: 100, Invitations, 100, 100, 1row 2: 100, RSVPs, 105, 148, 2etc(comma just represents next column) Would possibly like to bring it across to the invoice as a sentence, such as 100 x Invitations, 100x100mm, 1 sided. Ive already been able to put the column results into a sentence on the invoice sheet, but not using this code.3)Columns on the invoice sheet & the costs sheet will be the same. A, B, C, D, E.4)I hadnt realised it was only updating changes in column A. The invoice sheet would need to respond to changes of all 5 columns on the costs sheet.5)Data stored on sheet2 called 'costs'.6)Hmm, Im pretty sure the dependents part is not required. Thats what happens when you copy & paste things you dont understand.7)Nope definatley not sure it does what i want! I mean, i have tried it and it very roughly does what i want, aside from not updating changed info in the other 4 columns, and being more refined. But i dont know enough about code in excel to know if its the right way or if theres a better way.

1. Sorry - I edited my response and I guess you got to it before the edit - so the extra question:As you want the stored information updated, it is necessary that the invoice has a unique identifier that enables the code to find it in the list of stored data. Where is the unique ID - you have only mentioned four columns - quantity, description, size, & choice.

2. Reading your response, I am not clear where the data is first entered. In you original post I thought that data was entered on a worksheet containing invoice data, but now it appears that the data is entered on the worksheet named 'costs'

3. Getting back to basics - what is the aim of this macroIs it to create an invoice which lists one or more items - each item preferably being in a sentence such as "100 x Invitations, 100x100mm, 1 sided"

4. Is there only one workbook, or do you have one workbook per order

5. My thinking (that's whats left of the little there was ...) is that you need one workbook per order (the 'Order' workbooks)Each Order workbook is saved with a name based on the order number.Each Order workbook has two worksheets:Invoice and DataThe Data worksheet is used to enter one or more items required for that order, including costs.The Invoice worksheet is formatted as an invoice - it takes data from the Data worksheet and joins data items together as required to make the information customer-friendly , has multiple lines for multiple items in the order and has some cost information - total for order, sales tax, shipping & handling etc. and of course the order number and customer name, address (billing & shipping) and so on.

The Invoice worksheet is always updated when the Data worksheet is changed (this does not require a macro)

There is then a summary Workbook which maintains one row per order - Invoice number and summary of each order.This summary is updated automatically when an Invoice is printed, or when a button is clicked - it could be one button on the Invoice worksheet which will save the summary data to the Summary workbook and will print the Invoice.

Oh yeh missed the edit1) Im not sure what the unique identifier would/could be. Sheet named 'costs' contains a list of items in column A. ie. invites, rsvp, place cards, menu, etc, which would be a standard/fixed list and other items could be added to the list if a new item/situation arose. If the quantity is more than zero then it should appear on the 'invoice' sheet. And if quantity is zero or blank it should be left off.2)Original data is entered on sheet named 'costs'. This sheet calculates my printing costs through some calculations and adds in other expenses to bring me to my invoice total, but all that needs to be displayed on the 'invoice' sheet is the info in columns A, B, C, D, E, and then the total cost, referenced separatley.3)The aim is to bring across only items which have 1 or more entered as a quantity and then reflect the related data, and change it and the quantity if it changes. Each row on 'costs' sheet to be a separate row in 'invoice' sheet. But if a quantity is 1 and then later changed to 0 the row should be removed from the 'invoice' sheet and following rows to be bumped up a row. Thats the bit that i think might be tricky.4)Not sure how to go about this. Im thinking i may enter the data in 'costs' sheet for each client, using the workbook as a template and perhaps saving one workbook per client, otherwise once the invoice is printed i will have to re-enter all data to make amendments.5)Yes i totally agree with your thinking! and hadnt thought of a summary workbook but this would be a good way of keeping track of everything.Cheers!

I have written a macro that should meet your initial requirements of adding, changing or removing items from the "invoice" worksheet, as data is added or changed on the "costs" worksheet.

I suggest that you start with a new workbook and try this out before trying to change addresses to match your exact layout.

Open a new workbook.Rename two worksheets to "invoice" and "costs" (don't use the double quotes when entering the worksheet names)Save the new workbook as an Excel Macro Enabled Workbook "InvoiceMacro.xlsm"

This macro responds to any changes in data on the "costs" worksheetIf the change is in the data input range A2 to E22, then the rest of the code runs.Rather than finding and deleting or updating data on the "invoice" worksheet, it just clears the data from the range B6 to B26, and creates a new list based on the changes you have made, selecting only from rows on the "costs" worksheet that have a quantity of greater than zero.

Once this is working you can start adjusting ranges for input data and invoice text to match your detailed requirements.

As you suggested this could become a template with each new workbook named for the customer - or preferrably a Customer Order number, so that further orders from the same customer will not over-write a previous order.

It would then be possible to add a button to the invoice that prints the invoice and saves summary data to a common Summary workbook.

If you would like, I can send you the sample workbook I have used.To do this send me a private message with an email address (do not post your email address in a response).

Hey Humar,Thanks for the email. At first i thought the file didnt work (i shouldnt have doubted you!), my computer must have blocked the macro so it wasnt updating from one sheet to the other, but then i followed your instructions and made a new workbook and its perfect! Now i'll get to making it work with my existing workbook. Thanks so much for taking the time to do this for me i seriously appreciate, its an enormous help and saves me weeks/months! Thankyou thankyou thankyou!

Which part of the code stipulates to take the data from column F?besides the part that says 'Col.F'. I thought this line beginning with ' might be commented out and not actually code. I have my 'order text' in column O as there are other formulas occupying F onwards. I did try changing the F to O on the off chance it wasnt just a comment. But no luck.

any idea what would stop the worksheet code from activating. Macros are enabled, and other macro buttons on the sheet work. But any updates to the sheet are no longer reflecting. Have closed, reopened many times.The invoice is populated with informating that was previously there, but even if i delete all 'order text' from the costs sheet the invoice remains populated with the outdated info, i must clear it manually and then any new information entered is not reflected.I feel like ive checked all the basic stuff that it could be. I havent messed with the worksheet code at all, aside from changing columns & row numbers, after which it all worked perfectly. Since then its remained untouched but is just not updating.Any ideas?

Code that uses events to trigger itself, has to disable events so that changes the code makes does not re-trigger it in an endless loop. Sometimes the code stops without re-enabling events. The code I wrote included an error trap that re-enabled events - but sometimes the code is stopped without the error handler running.

Try this:Open the Visual Basic Window.From the menu bar select View and check the 'Immediate Window'Scroll down to the bottom of the Immediate window - there may be some instructions showing in the window.Now type in:Application.EnableEvents = TRUEand hit Enter.

ahhh ok.....first time i tried what you said, it came up with an error saying the cell that it was trying to change was protected, so i unprotected, re-did what you said and it works.....is there anyway to have it protected and still work?

If the "invoice" worksheet is protected you can use these lines before and after the code that makes the changes:Worksheets("invoice").Protect Contents:=False.... make changes to cells here ....Worksheets("invoice").Protect Contents:=True

If the worksheet was protected with a password you will need to include the password in both linesWorksheets("invoice").Protect Contents:=False, Password:="PW"...Worksheets("invoice").Protect Contents:=True, Password:="PW"

This of course makes your password accessible to anyone who can open the visual basic window, so you need to lock your visual basic code so that it cannot be viewed.

In the Project explorer pane of the visual basic window (the pane with a list of workbooks and worksheets), select the "invoice" worksheet, listed under Microsoft Excel Objects - for your workbook.Right-click the name and select 'VBA Project Properties' and in the Protection tab check 'Lock project for viewing' and enter a password.

The information on Computing.Net is the opinions of its users. Such
opinions may not be accurate and they are to be used at your own risk.
Computing.Net cannot verify the validity of the statements made on this
site. Computing.Net and Purch hereby disclaim all responsibility
and liability for the content of Computing.Net and its accuracy.