VBA Macro that converts and saves the worksheet into a PDF & customizing the filename

[jonvanwyk]

I am presently using the code below to convert a worksheet into a PDF and then automatically save it to a specified location on a shared drive. The down side to the code below is that it must be modified each year to accomodate for a new FY. I am trying to figure out a different way of saving the file so that the code does not need to be changed each year.

The year (highlighted in red below) of the file is located in cell "F6" on worksheet "Menu". The date range (also highlighted below) for this particular worksheet is located in cell "B9" of worksheet "Menu". Is there a way to modify the code below so that rather than tell it to save in folder "2012", subfolder "01 = 061911 - 070211", that it would instead look at "F6" and "B9" and know where to save it based on their values?

Please Login or Register  to view this content.

[royUK]

Something like this

Please Login or Register  to view this content.

[jonvanwyk]

Thanks for the help Roy. I believe I should have included the code for the macro in its entirety. My post above only included the portion I thought I would need to change. Below reflects the whole code, with the changes you recommedned. I am getting a debugging error when I try to use it. Thoughts?

Please Login or Register  to view this content.

[jonvanwyk]

I think I might have figured out why I am getting a debugging error. The folder for that particular period of time is labeled "070111 - 071411", but the data in "B9" is "07/01/11 - 07/14/11". Since a folder cannot contain "/ or \", it is not finding the correct path. I wonder if there is a way for the code to take the dates with slashes, and convert it into a date without so that it can find the correct path and save the PDF?

[jonvanwyk]

Okay, I have updated the information in B9 to reflect the date range without slashes. It might also have helped if I had named the folder with the correct year. doh! But, all is well and the macro is working properly.