Help With Automated Invoices

[LandS]

I am looking to create a button in a row of data that i can click and will use the data in that row to auto fill an invoice which is a separate worksheet.

I have a worksheet for all my customers. In that row is their Customer Number, Name, Address, Phone and Email. I want to be able to hit one button that sends all that info into specific cells of another worksheet when I am making the invoice for them instead of retyping it every time. Any help would be greatly appreciated.

 

[dreid1011]

Welcome to the board. That can be achieved, however, I might suggest using a single button on the sheet, and selecting the row/customer you would like to generate the invoice for, instead of making a button for every row.

Could you provide sample data showing the precise layout of both sheets please? And which data should be transferred to the invoice sheet and where.

 

[LandS]

Here is a screenshot of what my client database looks like and my invoice. The areas in red in the invoice is the data i am trying to pull from the database.

 

[dreid1011]

What are the sheet names?

 

[LandS]

2024Database and 2024Invoice

 

[dreid1011]

Alright, give this a try. Create a Form button on your database sheet, add the following code to a standard VBA module, then assign the code to the button (Assign Macro). Select a row on your database sheet, then click the button. It should populate your invoice sheet with the relevant information.

Sub FillInvoice()
Dim cRow As Long
Dim wsDB As Worksheet, wsINV As Worksheet
Set wsDB = Sheets("2024Database")
Set wsINV = Sheets("2024Invoice")
cRow = Selection.Row
With wsINV
    .Range("C9").Value = wsDB.Range("C" & cRow).Value
    .Range("C10").Value = wsDB.Range("D" & cRow).Value
    .Range("C11").Value = wsDB.Range("E" & cRow).Value
    .Range("C12").Value = wsDB.Range("G" & cRow).Value
    .Range("C13").Value = wsDB.Range("H" & cRow).Value
    .Range("J3").Value = wsDB.Range("I" & cRow).Value
    .Range("J4").Value = wsDB.Range("B" & cRow).Value
End With
End Sub

 

[LandS]

Thanks, I appreciate the help. I'm sure it works I just can't seem to follow the steps. I got to the macro screen where the code guys, just not sure how to execute it or make a button.

 

[dreid1011]

You add a button through the Developer tab here:

If you don't have the Developer tab active, right click on a blank space on the ribbon and go to Customize Ribbon and enable it.

On the Developer tab, go to Insert, and choose the Form Button in the top left:

Once you place your button, it should prompt you to assign a macro, then you assign the code I provided to the button. Once you assign the code to the button, it should run when you click the button. (Note it may not do anything if "Design Mode" is active (see above screen shot next to "Insert"))