Articles

populate invoice-sheet1 with info from sheet2

August 23, 2010 at 01:32:22
Specs: Windows Vista

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!


Private Sub Worksheet_Change(ByVal Target As Range)
'Check if a value is entered in Quantity column (a) and that it is > 0
If Target.Column = 1 Then
If Target > 0 Then
'Determine next empty row in Invoice sheet
nxtInvRow = 1
chkInvRow:
If Sheets("sheet1").Cells(nxtInvRow, "a") <> "" Then
nxtInvRow = nxtInvRow + 1
GoTo chkInvRow
End If
'Copy data from changed row to Invoice Sheet
Sheets("sheet1").Range("a" & nxtInvRow) = Cells(Target.Row, "a")
Sheets("sheet1").Range("b" & nxtInvRow) = Cells(Target.Row, "b")
Sheets("sheet1").Range("c" & nxtInvRow) = Cells(Target.Row, "c")
Sheets("sheet1").Range("d" & nxtInvRow) = Cells(Target.Row, "d")
'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("sheet1").Range("a" & nxtInvRow) = Cells(depCell.Row, "a")
Sheets("sheet1").Range("b" & nxtInvRow) = Cells(depCell.Row, "b")
Sheets("sheet1").Range("c" & nxtInvRow) = Cells(depCell.Row, "c")
Sheets("sheet1").Range("d" & nxtInvRow) = Cells(depCell.Row, "d")
Next
NoDependents:
End If
End If
End Sub


See More: populate invoice-sheet1 with info from sheet2

Report •


#1
August 23, 2010 at 01:38:55

also if this is the wrong way to go about it please let me know! thankyou

Report •

#2
August 23, 2010 at 04:51:38

Hi,

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.

Regards


Report •

#3
August 23, 2010 at 05:35:48

Hi Humar thanks for the reply.

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 example
row 1: 100, Invitations, 100, 100, 1
row 2: 100, RSVPs, 105, 148, 2
etc
(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.

Hope thats clear, and thanks alot for having a look for me


Report •

Related Solutions

#4
August 23, 2010 at 06:24:50

Hi,

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 macro
Is 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 Data
The 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.

Any thoughts

Regards


Report •

#5
August 23, 2010 at 06:55:17

Oh yeh missed the edit
1) 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!

Report •

#6
August 24, 2010 at 00:32:20

Just incase i confused things, I think i mucked up my explanation of the columns in the last post.
Cost sheet shows columns A, B, C, D, E.
Description, quantity, width, height & drop down option, respectively.
Invoice sheet shows the columns in a sentence in this order. B, A, C, D, E

Report •

#7
August 24, 2010 at 06:39:18

Hi,

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"

On the invoice worksheet enter "Your order" in cell B5
Select cells B6 to B26 and format the cells borders with a grid
On the costs worksheet enter Description, Quantity, Width, Height, Option, Order text, Product list & Option list in cells A1 to H1
In cells G2 to G6 enter 5 product names (invites, rsvp etc.)
In cells H2 and H3 enter 1 and 2
Select cells A2 to A22, and add data validation to them, selecting the List option and using the range =$G$2:$G$6
Select cells E2 to E22, and add data validation to them, selecting the List option and using the range =$H$2:$H$3
In cell F2 enter this formula:
=IF(B2>0,B2&" "&A2&". Size "&C2&"mm by "&D2&"mm. "&E2&" sided.","")
Drag the formula to extend it down to cell F22

Size column B on the invoice worksheet and column F on the costs worksheet to accommodate the Order text description

Now right-click the "costs" worksheet name tab and select 'View Code'
paste this into the large Visual Basic code window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

'test if changed cell is in our item description area
'change description range area if necessary
If Not Intersect(Target, Range("A2:E22")) Is Nothing Then
    Dim rngCell As Range
    Dim intInvRow As Integer
    'disable events so that changes made by this macro
    'won't re-trigger it
    Application.EnableEvents = False
    
    'set invoice text area first row
    intInvRow = 6
    're-do invoice list from scratch with changed data
    'delete all existing invoice data
    'change invoice item list range as required
    Worksheets("invoice").Range("B6:B26").ClearContents
    'get new data from costs worksheet
    'change number of rows used in cost worksheet if necessary
    For Each rngCell In Worksheets("costs").Range("B2:B22")
        If rngCell.Value > 0 Then
            'valid quantity - so copy text in Col. F to next invoice row
            Worksheets("invoice").Range("B" & CStr(intInvRow)).Value _
                        = rngCell.Offset(0, 4).Text
            'increment invoice row
            intInvRow = intInvRow + 1
        End If
    Next rngCell
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Save the workbook

This macro responds to any changes in data on the "costs" worksheet
If 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).

Regards


Report •

#8
August 24, 2010 at 07:26:20

Hi Humar,

Thanks so much for your time. I will try this tomorrow and let you know how i go. Will send you a pm now with my email. Thanks again i really appreciate it.


Report •

#9
August 26, 2010 at 05:41:08

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!

Report •

#10
August 26, 2010 at 05:57:41

Hi,

Glad to have been of help.

Regards

Humar


Report •

#11
August 27, 2010 at 10:23:28

Hi Humar,

Im just trying to make it work with my workbook and wondering about this section in the code:

'valid quantity - so copy text in Col. F to next invoice row
Worksheets("invoice").Range("A" & CStr(intInvRow)).Value _
= rngCell.Offset(0, 4).Text

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.


Report •

#12
August 27, 2010 at 10:46:37

oh awesome sussed it, changed 4 to 13

Worksheets("invoice").Range("A" & CStr(intInvRow)).Value _
= rngCell.Offset(0, 4).Text

sorry for bugging ya!


Report •

#13
August 27, 2010 at 11:30:00

Hi,

Glad you worked it out.

All lines starting with ' are comments.
The Offset syntax is Offset(number_of_rows, number_of_columns)

Regards


Report •

#14
August 28, 2010 at 22:09:47

Hey Humar,

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?


Report •

#15
August 29, 2010 at 08:26:02

Hi,

It is possible that the Events-Enabled setting is set to False.

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 = TRUE
and hit Enter.

See if it works again.

Regards


Report •

#16
August 30, 2010 at 03:38:03

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?

Report •

#17
August 30, 2010 at 04:45:50

Hi,

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 lines
Worksheets("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.

Regards


Report •

#18
August 30, 2010 at 05:03:48

thanks. when you say before and after the code that makes changes, do you mean all the code pasted into the 'costs' sheet or is there a particular part i need to single out?

Report •

#19
August 31, 2010 at 08:15:32

Hi,

I would put the first line before this:

're-do invoice list from scratch with changed data

and the second line after this:
    Next rngCell

Regards


Report •

#20
August 31, 2010 at 08:32:47

thats amazing you know your shiz...thanks!

Report •

#21
September 1, 2010 at 13:28:57

You're very welcome.

Regards

Humar


Report •


Ask Question