Solution for archiving invoices

[Kokas]

Hi guys and girls!

I am a kind of a self taught newbie to excel.

I created an excel worksheet to help me work with my invoices that looks (and works) pretty cool.

However i need help creating an archive sheet that will include all the data from every invoice i issue.

I would need a button to activate the transfer of data to the archive sheet because i use the invoice sheet as a template to issue all of my invoices...

The archive sheet would include the date issued, the number of the invoice, the amounts to be paid, the vat and the company details that i am issuing the invoice to...

Please help, i will be more than grateful !!

Thanx!

 

[mumps]

You could possibly have the archive updated automatically when you issue the invoice. It is difficult to suggest anything without seeing how you data is organized and what your template looks like. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Kokas]

You could possibly have the archive updated automatically when you issue the invoice. It is difficult to suggest anything without seeing how you data is organized and what your template looks like. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

Ok then...

The link to the TEST file is here

The invoice is written in Greek so i will try to make it clear

What i want to do is already almost done. At the page "Αρχείο" (means archive) i already associated the appropriate cells of the page "Τιμολόγιο Υπηρεσιών" (means services invoice)

What i am missing is a command (with a button maybe)or a formula to record this values from the "Τιμολόγιο Υπηρεσιών" page to the "Αρχείο" only when i want to...Actually i want it updated by the time i issue the invoice as you said above...

What are my options ?

Thank you!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet Τιμολόγιο Υπηρεσιών and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This is a Worksheet_Change macro which is triggered automatically when you make a change in cell E9 in your case. This means that you must make sure that all the other data on your Τιμολόγιο Υπηρεσιών sheet has been entered and the last thing you do is you make your selection in the drop down list in E9. Also, you no longer need the formulae in columns A to D in the archive sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E9")) Is Nothing Then Exit Sub
    With Sheets("Sheet2")
        .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Range("H5")
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Range("H3")
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Range("E9")
        .Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = Range("H18")
    End With
End Sub

 

[Kokas]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet Τιμολόγιο Υπηρεσιών and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. This is a Worksheet_Change macro which is triggered automatically when you make a change in cell E9 in your case. This means that you must make sure that all the other data on your Τιμολόγιο Υπηρεσιών sheet has been entered and the last thing you do is you make your selection in the drop down list in E9. Also, you no longer need the formulae in columns A to D in the archive sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("E9")) Is Nothing Then Exit Sub
    With Sheets("Sheet2")
        .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = Range("H5")
        .Cells(Rows.Count, "B").End(xlUp).Offset(1, 0) = Range("H3")
        .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0) = Range("E9")
        .Cells(Rows.Count, "D").End(xlUp).Offset(1, 0) = Range("H18")
    End With
End Sub

Ok this sounds cool enough !! However, i did as described but nothing seems to happen...

 

[mumps]

My apologies. I used "Sheets("Sheet2") in the code when I was testing it. Change

With Sheets("Sheet2")

to

With Sheets(2)

and try it again.

 

[Kokas]

My apologies. I used "Sheets("Sheet2") in the code when I was testing it. Change

With Sheets("Sheet2")

to

With Sheets(2)

and try it again.

I hope i don't do something wrong but it still doesn't work

 

[mumps]

Click here to download your file and give it a try. Just make a selection in E9.

 

[Kokas]

You're right ! It works like a charm ! Thank you !

 

[mumps]

You are very welcome.