Report

Macros: Copy Invoice Details From One Sheet to Another [Solved]

Ask a question Jenny - Last answered on Oct 20, 2016 05:42AM
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.
See more 
Helpful
+0
moins plus
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, ge.tt 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,

Cheerio,
vcoolio.
Add comment
Helpful
+0
moins plus
Thank you for this, it definitely sound like what I need. I've typed this all out with the correct cells & sheets names however I am now getting an error.
Here's the whole macro. Would be grateful if you could show where I've gone wrong

Sub PrintForm()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Dim num As Integer
Range("J7").Select
num = Range("J7").Value
num = num + 1
Range("J7").Value = num
Sub SendToLog()

Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Payment Sheet")
Set WS2 = Worksheets("Payment 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("J7"), WS1.Range("I8"), WS1.Range("E15"), WS1.Range("E19"), WS1.Range("E17"), WS1.Range("G2"))

Application.ScreenUpdating = True

End Sub
Add comment
Helpful
+0
moins plus
Hello Jenny,

Two aspects come to light which are probably giving you errors.

1) In your code ( and I assume that it works ok for you), you have left out the End Sub line and the name of the new code needs to go above End Sub of your code.

2) You are taking data from six cells in the source sheet whereas the code I supplied takes data from only three cells based on your opening post. Hence you need to resize columns 1 - 6 in the destination sheet.

Thus, the codes together should look as follows:-


Sub PrintForm()
 
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
Dim num As Integer 
Range("J7").Select 
num = Range("J7").Value 
num = num + 1 
Range("J7").Value = num 

 SendToLog 

End Sub

Sub SendToLog()

     Dim WS1 As Worksheet 
     Dim WS2 As Worksheet 
     Set WS1 = Worksheets("Payment Sheet") 
     Set WS2 = Worksheets("Payment Log") 

Application.ScreenUpdating = False 

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

 WS2.Cells(NextRow, 1).Resize(1, 6).Value = Array(WS1.Range("J7"), WS1.Range("I8"), WS1.Range("E15"), WS1.Range("E19"), WS1.Range("E17"), WS1.Range("G2")) 

Application.ScreenUpdating = True 

End Sub


They can be placed together in the one module. You may have to re-assign the PrintForm sub to your button.

Cheerio,
vcoolio.
Jenny- Oct 20, 2016 05:30AM
That's perfect thank you, fully working now
Reply
Ambucias 35151Posts Monday February 1, 2010Registration date ModeratorStatus December 6, 2016 Last seen - Oct 20, 2016 05:33AM
Another miracle for vcoolio !
Reply
vcoolio 831Posts Thursday July 24, 2014Registration date ModeratorStatus December 4, 2016 Last seen - Oct 20, 2016 05:42AM
You're welcome Jenny. Glad that I was able to help.

@Ambucias:

Glad that you are impressed, Master!

Cheerio People.
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!