Create PDF from Multiple tabs from Multiple Workbooks

[sp43]

I am needing to create a macro that copies a range of cells from 2 different tabs in several different workbooks and creates 1 pdf file. I have 0 knowledge of VBA and don't know where to start. Any assistance would be great appreciated. This is done several times a week and really eats up time for the people that have to do it (which is not me). Why I am being tasked with creating the macro since it won't benefit me at all is a great question I do not have the answer to.

 

[John_w]

Do you still need help with this? The basic code could be generated using the macro recorder and then edited and refined.

The "several different workbooks" and their sheet names could be hard-coded in the macro, or specified in cells in the macro workbook, or you could use a Dir function loop which loops through the workbooks in a folder. Copying the range of cells - do you want just the cell values, or the cell formats, column widths, row heights as well? The former can be done with a simple destinationRange.Value = sourceRange.Value assignment, whereas the latter needs Copy and PasteSpecial.

 

[sp43]

Do you still need help with this? The basic code could be generated using the macro recorder and then edited and refined.

The "several different workbooks" and their sheet names could be hard-coded in the macro, or specified in cells in the macro workbook, or you could use a Dir function loop which loops through the workbooks in a folder. Copying the range of cells - do you want just the cell values, or the cell formats, column widths, row heights as well? The former can be done with a simple destinationRange.Value = sourceRange.Value assignment, whereas the latter needs Copy and PasteSpecial.

Thanks for the response. I tried using the macro recorder, but it keeps saving over the first sheet when the second sheet gets saved. How can I get them to be in the same pdf file?

 

[John_w]

How can I get them to be in the same pdf file?

There are 2 ways:

1. Copy every range to the same temporary sheet in the macro workbook, in the next available row, so that the ranges are one below the other in the sheet. Then save the temporary sheet as a PDF. The ranges will run one after the other in the PDF, unless you add page breaks before saving it.

2. Copy each range to a new sheet in a new temporary workbook, so that there is one sheet per range. Then save the temporary workbook as a PDF. Each range will start on a new page in the PDF. The advantage of this method is that you can copy and preserve the column widths of each range, since each range is copied to its own sheet.