Set range and title pdf based on cell values

[moosetales]

Good day. I need to find a way to set multiple ranges dynamically (I believe that's what it's called) and then save the data in each range to a separate pdf file that is named based on a specific cell value.

See example below.

Pic.jpg

I would like to set the range based on x's being placed in column A

The code I'm using to save the files as pdfs is as follows:

Please Login or Register  to view this content.

Here is a mock up of my workbook.

TEST.xlsm

Thanks.

[walruseggman]

You'll need to change the start of the file name Fname to suit your needs. Obviously I can't test it with the file path you have in your example code, and I'm not sure that's where you want the files, anyway.

Please Login or Register  to view this content.

[moosetales]

walruseggman,

Brilliant. It worked first run. Thank you. I'm marking this thread as solved but if you wouldn't mind answering one more question that would be great and if not I understand.

I'm trying to add a message box to the code you provided so the user can opt to save the files on the share drive or not and when I click "Yes" I keep getting the following run-time error:

pic.jpg

Here's the code as it sits now:

Please Login or Register  to view this content.

[Alf]

Try adding this line to the macro

Please Login or Register  to view this content.

and you can also delete the ".pdf" part in line

Please Login or Register  to view this content.

as saving the file as xlTypePDF will add the ".pdf" part on its own.


Alf

[moosetales]

Alf,

That did the trick. Thank you kindly. Also, thanks for the tip regarding ".pdf".

Matthew

[Alf]

You are welcome.

Thanks for feed-back and rep

And as Excel is a most flexible program your problem could be solved in a slightly different way i.e.

Please Login or Register  to view this content.

Macro starts by finding the last value in the A column, then it goes from A1 and down till it finds the first value in the A colunm, as this will be the active cell the command
"ActiveCell.Row" will give the row number, using this value it makes a second jump down the A column and activate the cell where it finds the next value i.e. k.

The fName is the path (hdd + folder) + Cells(j, 2).Value. Cells is defined by row and column so in the first case where j=2 the Cells(j, 2) is equal to cell vaue of B2 i.e.
"Micky Mouse"

And the range to export as a pdf file in the first case is the range B2:E7, and since k = 8, Cells(k -1, 5) is row 7 and column 5 i.e. E7.

Then macro loops untill k = i

So as you see a slightly different approach but is it better?? Don't know as this macro reacts on any value found in the A column where as walruseggman's macro only reacts if there is an x in the A column.

Alf

[moosetales]

Alf,

Being a novice with VBA it will take me a bit to digest your feedback but I really appreciate you providing the alternate code and the tutorial. I do like the fact that your code doesn't require an "x" given the fact that other users will be inputting data and they may elect to use a different value. I'll give your code a try. Thanks.

Matthew