Help With Automated Invoices

LandS

New Member
Joined
Jan 19, 2024
Messages
4
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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.
 
Upvote 0
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.
 

Attachments

  • Untitled-2.jpg
    Untitled-2.jpg
    201 KB · Views: 17
  • Untitled-1.jpg
    Untitled-1.jpg
    199.2 KB · Views: 25
Upvote 0
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.

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
You add a button through the Developer tab here:
1705710354793.png


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:
1705710467149.png


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"))
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top