Help modify macro to save as xlsx instead of pdf

Hi everyone. I have a spreadsheet that is pretty big, pretty complex, and it is challenging to learn new techniques with it.

Currently we run the numbers and publish a technician pick sheet in pdf. That sheet only shows what he rates that we have on hand.

We also publish a sheet specific to that tech showing all the background information not seen by the picker on the warehouse floor in pdf format. That is handy when someone asks a question about that specific date/time/tech.

And, we publish a stripped down pdf showing what he was owed no matter what was in or out of stock.

The third out put is what I would like to experiment with. I've googled it. I searched this forum. But, to be honest I am not finding an easy edit that I can understand at my skill level.

Here is the current macro. The only thing I want to change is that the output file is a workbook I can edit instead of .pdf that gets converted back to excel from time to time. So the Ranges modification and the various calls will have to stay in place. I especially need to keep the filename as shown. P6 is basically "techname & ponumber & "backfill" all concantentated to the cell. This provides a unique and searchable way to quickly get to a specific pick sheet months later. So, if we can't do that then I need to keep what works.

I'm looking forward to learning a lot of new stuff. Thank you for taking the time to look at this.

Please Login or Register  to view this content.

Thank you! I appreciate it. Apparently it doesn't like something in there. I tried it and got an error message "object doesn't support property or method" so I will be google searching tomorrow.

I’m good sir and I owe it all to you. When I inserted your code and ran it the first time it saved the current worksheet with the correct filename. Taht’s when I realized it was saving the whole sheet which included command buttons, links to external files, dynamic stuff that suddenly had #ref errors, and changing the data source on two pivot tables. I was seriously glad I was working on a sandbox copy instead of the daily driver.

What that did was make me realize that what I asked for wasn’t really what I was looking for. My three out put files were static snapshots which the export to pdf code did perfectly. The third file needed to be a bare bones editable snapshot. So, I took your code and tried to figure out how to copy, paste only values, then save as a new worksheet. I never did find the answer.

That led me to adding a helper worksheet. I copied the three columns and pasted values only to it. THAT worksheet is what got copied, named, and pasted as a new workbook.

I meant to post my code but I also remembered to say thanks while I am away from my PC. I’ll try to remember tomorrow to post the code. Right now I am looking at the select command. I read somewhere that I could tweak the code and speed it up. BUt, for tonight I am up and running. And, I learned a lot that began with the code you helped me with.

Thank you very much. If you celebrate Christmas I wish you a Merry Christmas and a happy, healthy, and prosperous New Year for you and all of yours. If you done’t celebrate Christmas, I still wish all of that for you.,