Macro to save selected worksheets as workbooks in variable location

[Groovicles]

Hi everyone,

I have the following code:

Please Login or Register  to view this content.

Right now the code will save each worksheets in the workbook as a separate PDF in the predefine location. What I would to do is:

1. Have the macro only save the active worksheet(s) in the workbook (i.e., the ones I've selected and are highlighted in white in the workbook)
2. Have file explorer open so I can select where I want the PDFs to be saved.

Any ideas?

Thanks everyone!!!

[AlphaFrog]

Please Login or Register  to view this content.

[Groovicles]

Hi AlphaFrog,

Thanks very much for the code. It nearly works perfectly, except for an interesting bit:

If is select 4 worksheets in the workbook, the code will correctly create 4 PDFs and using the worksheet name and put it in the folder I select.

However,

When I check each PDF, each PDF actually has all four worksheets published in them. So for example, say I have worksheets 1, 2, 3, and 4. The code will publish 4 worksheets called 1, 2, 3, 4, but within each PDF has all four worksheets. What I was going for is to have each worksheet selected PDF'ed with only the contents of that particular worksheet.

Any ideas?

Thanks!!

[AlphaFrog]

Try this...

Please Login or Register  to view this content.

[Groovicles]

Hi AlphFrog,

It works perfectly. Thanks very much. I understand your code except for the wsArr. Could you explain to me what that does? I'm not sure I understand the difference between:

Dim ws as worksheet
and
Dim wsArr as Sheets.

Thanks!!!

[AlphaFrog]

You're welcome.

The variable wsArr is used to store the collection of selected sheets because later each sheet is selected one-at-a-time so the ws.ExportAsFixedFormat saves the one selected sheet. Then after all the sheets are exported, wsArr is used to reselect all the original selected sheets.

It's not really needed except to restore the original selected sheets. You could remove the three lines of code that include wsArr and it would still work except only one sheet is selected in the end.

[Groovicles]

I see. that's great. Thanks very much for your help.