I had a similar requirement some time ago. What I ended up doing was to
save the sheet as an xls file (for which you can build up the filename
as necessary in a macro) and then change the printer to the PDF printer
and print the xls file to this driver (then reset the printer
afterwards). It was not fully automatic, however, as I still had to
confirm manually which folder to save the file in.

Hope this helps.

Pete