Macros: Copy Invoice Details From One Sheet to Another

Jenny - Oct 19, 2016 at 11:20 AM
vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 - Oct 20, 2016 at 05:42 AM
Would be very grateful for help with this one
I have set up a macro that will print and generate the next reference for my invoices raised in excel, however I would like to add in one more Macro but am struggling on this one.
I basically needs to take certain cells from the invoice i.e. Inv #, Date, Value and copy it onto a log once the Macro button is clicked.

3 responses

vcoolio Posts 1409 Registration date Thursday July 24, 2014 Status Moderator Last seen May 23, 2024 262
Oct 19, 2016 at 09:57 PM
Hello Jenny,

I assume that you would like to create an archive or register of all invoices created and sent out.

As I don't know the set out of your work book, I've made some assumptions and think that the following code may work for you:-

Sub SendToLog()

    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Invoices")
    Set WS2 = Worksheets("Log")
Application.ScreenUpdating = False

    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

    WS2.Cells(NextRow, 1).Resize(1, 3).Value = Array(WS1.Range("L10"), WS1.Range("C10"), WS1.Range("L9"))
Application.ScreenUpdating = True

End Sub

As I don't know in which cells the required data resides, in lines 12 and 13 of the above code, I've made up some cell references: L10 may be the invoice number, C10 may be the date and L9 may be the invoice value. You'll need to change these cell references to suit yourself.

(Do you perhaps also need to add the Customer name to the "Log" sheet?)

I've also assumed that the sheet names may be "Invoices" as the source sheet and "Log" as the destination sheet. Again change these to suit yourself. The above code also assumes that, in the "Log" sheet, Column A is for the Invoice number, Column B is for the date and Column C is for the value.

You can add the above code to the same module as your first code but call this code from your first one simply by placing the name of this code (SendToLog) just above End Sub in your first code.

If this is a little confusing for you, then please upload a sample of your work book to a free file sharing site such as DropBox, or SpeedyShare then post the link to your file back here. We can then have a proper look at your query.

I hope that this helps,