Macro to copy and paste an excel range into a word doc and then print to PDF??

**Weswold** · 12-01-2012, 05:08 AM

Hi all,

Complete newb so please bear with me.

Somebody has attempted this at the co I work for and it just doesn't work properly.

I'm really new to the vba world but I know there will be a solution to this problem somewhere so thought I'd put it to you all for comment/ feedback.

The co use an excel sheet as the basis of their order, this is then copied and pasted into a word doc that has the co's logo header and footer. They then print this to PDF for emailing?

This is so long winded and I'm not convinced its the best way of doing things. That said I only started this week and really want to have a go at solving this four month old problem!

Any guidance would be greatly appreciated!

Thank you in advance of any response.

Wes

**TMS** · 12-01-2012, 05:16 AM

Why do you copy it to a Word document? You could have a template in Excel that has a header, footer and company logo. And, as you're using Excel 2010, it is easy to save this as a PDF file. That can then be attached to an email ... all in one macro.

See this thread for ideas:

http://www.excelforum.com/excel-gene...k-1-macro.html

Also note that the Invoice file I refer to was originally a Word template that I copy, pasted and tweaked in Excel so that is very much an option.

Regards, TMS

**Weswold** · 12-01-2012, 11:04 AM

Thanks for the response TMS, the only reason they copy to word is because there could be as many as 80 orders on one project, if you had the co logo on each order in the workbook it would end up as a huge file.

Really appreciate your guidance though, I'll perceiver with the link you sent and see where I end up. If anyone has anything else to offer it would be hugely appreciated.

**TMS** · 12-01-2012, 11:34 AM

I'm not entirely sure how you would envisage it working. In my mind, there would be an Invoice template with footer, header and company logo ... this would perhaps cater for 20 line items (orders). So, you'd loop through the orders and produce a copy of the template for every 20 orders; therefore, in this case, you'd have four "pages" of invoices for the project. You'd then select the four "pages", produce a pdf for them (combined, one pdf, four pages) and then delete them. So the workbook would grow temporarily, but you'd delete the additional sheets generated as soon as you've created the pdf file.

Regards, TMS