vba code to create multiple invoices from drop down list after looking up data

[wnorrick]

I am trying to set up a way to produce an invoice for over 200 accounts working from the invoice template, a drop down list for each account and a worksheet that looks up values based on each account. I have attached a mini sample. on the invoice if you select one of the accounts it does a look up on the property tab to get data on number of beds and property code then enters that on the invoice. It also does a look up based on the Annual tab to determine costs. The idea is to select a property/account, it populates the invoice and then it either needs to automatically print or preferable go to it's own tab. There will be over 200 of these so doing them individually will be time consuming. Is there a way to automate some of this or all of it? Is there a better way to set it up? i have thought about setting up over 200 worksheets and then linking information from a master worksheet somehow. So even VBA that would duplicate the invoice template for each property in the drop down or the list on the property tab would help some. This one will be annual but will need a similar one for monthly.
i am just going in circles on the best way to go about this. All suggestions are welcome.
Thank you, Wanda

 

[Nebu]

Hi:

Use the following code.

Sub test()
With Sheet1
For I& = 1 To .Cells(Rows.Count, 15).End(xlUp).Row
.[C9] = .Cells(I, 15).Value
.Range("A1:H42").PrintPreview
Next
End With

End Sub

Change ".PrintPreview" to "PrintOut" for printing the invoices out.
Note: The list in column O is for looping through all the available properties.

Thanks

 

[wnorrick]

Nebu, I just tested this and it works great. Thank you so much for your help. This will be an enormous time saver with over 200 needed and I learned something new.
Wanda

 

[wnorrick]

Help. This worked great to print out the 200+ invoices. Now, however, i have been asked if I can do two additional things with the invoices. One, create a total of the billed amount and two perform the same procedure on a second invoice in the workbook. So for the total of billed amount it would be great if the total of each invoice posts to another worksheet along with property name as the macro goes through the loop, is that possible? For the second part iIjust need to know how to make it perform the procedure on a different invoice template in the same workbook. Also, is it possible to have each invoice as it is calculated either print to a pdf or copy/paste to a new worksheet?
I really appreciate all of the help available on this forum and how great people are to help others learn more in all things excel.
Wanda