Delete all sheets except three

[Dean]

Last week, you folks clued me onto a wonderful, free PDF creator. Often, I
want to create a three page PDF from 3 of the worksheets in my EXCEL
template and it doesn't seem that I can highlight only those three sheets
together and make it happen. However, if I save a copy of my excel file
with all the other sheets deleted, after paste specialing values into the
three output sheets, of course - not a big deal to do via a macro - then it
seems quite able to print all the three sheets remaining in the revised
EXCEL file as a single PDF. In other words, it seems able to print all the
sheets into a single PDF, or just one sheet, but nothing in between. Or am
I wrong?

Regardless, I just did such a macro and it worked. The only problem is
that, I add additional sheets to some files and even change the names of the
other sheets (not the three in question). So, I need a macro subroutine
that will say, if the sheet name is anything other than Joe, jack, and
Harry, delete it. Can anyone help me with this?

Thanks much!
Dean

 

[Dean]

Actually, I was just able to combine 2 out of 3 sheets into one PDF just
now. I can't figure out why, when I hit file, then print in EXCEL, it
unselects more than one worksheet, and only sometimes, it seems. But I am
thinking that deleting sheets, even via macro, is dangerous, in that I might
resave the file under the same name accidentally. So, I am hoping you folks
can tell me some way to select any number of worksheets using the PDF
creator and have EXCEL accept all those sheets into a SINGLE PDF file,
which, by the way, I got from sourceforge.net.

Thanks!
Dean

 

[Corey]

Try:

Application.DisplayAlerts = False
On Error Resume Next
Dim myrange As Range
For Each sh In ThisWorkbook.Worksheets
If sh.name <> "Joe" And sh.name <> "Jack" And sh.name <> "Harry" Then
sh.Delete
End If
Next
Application.DisplayAlerts = True


Corey....
Last week, you folks clued me onto a wonderful, free PDF creator. Often, I
want to create a three page PDF from 3 of the worksheets in my EXCEL
template and it doesn't seem that I can highlight only those three sheets
together and make it happen. However, if I save a copy of my excel file
with all the other sheets deleted, after paste specialing values into the
three output sheets, of course - not a big deal to do via a macro - then it
seems quite able to print all the three sheets remaining in the revised
EXCEL file as a single PDF. In other words, it seems able to print all the
sheets into a single PDF, or just one sheet, but nothing in between. Or am
I wrong?

Regardless, I just did such a macro and it worked. The only problem is
that, I add additional sheets to some files and even change the names of the
other sheets (not the three in question). So, I need a macro subroutine
that will say, if the sheet name is anything other than Joe, jack, and
Harry, delete it. Can anyone help me with this?

Thanks much!
Dean

 

[Dean]

Thanks much, Corey. This seems to work (after I dim'd the sh variable)
though I learned a lesson that the sheet names in the macro are case
sensitive. The only other thing that would be nice, is if someone can tell
me how to have the macro, at the outset, rename the file with "-PDF" (before
the .xls) at the outset, so I don't have to worry about overwriting the main
file.

As I mentioned in a follow-up post, though this will help for now, what
would be better yet would be if I could actually just have it print the
three sheets I want it to as a single PDF file, without having to delete all
the other worksheets - that way I would not need to create a smaller file
and change its filename as above. Does someone know, using that free
PDFcreator, how to tell it to take three worksheets and create the PDF from
only those? I assume the right macro could somehow do it?

Thanks!
Dean