Saving excel as pdf with macro

[tchappell]

I currently have about 100 companies that each have 2 excel files. Each quarter I need to take 1 tab from one book and insert it into the other book and save that entire book as a pdf. I tried doing a macro for some of this but what happens when I go to save the file is it replaces the original pdf file with the next one I'm in. So the macro is always saying "save as "book1.pdf"" instead of changing it to the file name i'm in, like "book2.pdf". How to do I adjust the code to keep the file name relative to the file that's open? Hopefully this is making sense. :/ All the tabs in both workbooks are exactly the same in every file. Thanks for any help, I know there has to be a faster way to consolidate these files.

 

[TheRobRush]

add a userform before the save that asks for a desired file name. store that, and then use it as the save name when vba is creating the save