Deleting Tabs from Workbook

Glenn,

Very nicely put together. That is code is doesn't require you to know the names of the sheets and therefore makes it a very reuseable macro.  I like the touch at the end of sending a message. Not how necessary since the tabs will not be there anymore but I do like it.

Thanks.  I do like knowing that a subroutine completes correctly, even if it takes a split second!

pony10us,

Thanks for jumping back in!

So,

Sub_DeleteTabs
Application.displayalerts = False
Worksheets ("????").Delete                       ' The names are actually Cell References on the master Tab
Application.DisplayAlerts = True
End Sub

How do I represent the Cell Reference that is associated with the Tab Name?

Thank you,

B.

Glenn, Pony,

Let me try Glenn's code here....just saw it.

B.

Bright,

Please look at Glenn's code.  It will do it without having to worry about the name of the tabs. It will remove everything except the main (first) sheet. You should be able to just copy it as another sub and maybe create a second button to click to run it.

Great!!!  This Workbook just keeps getting better thanks to you guys.  Appreciate your hard work and great thought on how to put the right 1s and 0s in the right order.

Appreciate your willingness to work with me.

One quick final question;  If I want to print out each "range" how do I do it with the code below?  I have 3 Categories, is it ("Category1"), ("Category2") ........... or ("Category1, Category2, Category3")?

Sub PrintRanges()
    PrintOutRange ("Category1")
End Sub

Not for points as Glenn & Pony seem to have this tied up.

As this won't be reversible, deleting sheets never is with a macro or otherwise, is it worth putting a check message at the beginning. One inadvertent click could be the ruin of all work.

Insert before switching off alerts:

Msg = MsgBox("Are you sure?", vbYesNo, "Check")
If Msg = vbNo Then Exit Sub

Select allOpen in new window

Thanks
Rob H

Rob,

Thank you!  I actually had put that in (I'm learning!!).  I did have one other quick question about how do I print output from multiple ranges.

One quick final question;  If I want to print out each "range" how do I do it with the code below?  I have 3 Categories, is it ("Category1"), ("Category2") ........... or ("Category1, Category2, Category3")?

Sub PrintRanges()
    PrintOutRange ("Category1")
End Sub

If there is a simple answer, I'd appreciate the quick help.

B.

I believe the proper method is the second option:

PrintOutRange("Category1,Category2,Category3")

My last post is incorrect.  You should quote (") each sheet name:

PrintOutRange("Category1","Category2","Category3") however there are lots of examples on the internet of writing a full script for this function that may be better suited to your needs.

Take a look at this one:   http://www.teachexcel.com/free-excel-macros/m-58,Print-Specific-Pages-in-Excel.html

Here's what I have but doesn't seem to work;

Sub PrintSurveyRanges()
    PrintOutRange = ("Category1""Category2""Category3""Category4""Category5")
End Sub

B.

You are missing the commoa between each catagory

When I add the comma in I get a compile error.

Sorry.

Try this: (I created a button on the first worksheet titled print and assigned this macro and it worked)

Sub CommandButton_Click()
    Application.ScreenUpdating = False
     
    Sheets(Array("Category1", "Category2","Category3","Category4","Category5")).PrintOut , , 1
    Sheet1.PrintOut , , 1 'use this method to print all together at the end instead printing individually.
    Application.ScreenUpdating = True
     
End Sub


This wiill bypass the print dialog as well

I used it but get a "Script out of Range" error.

on this line:

    Sheets(Array("Category1", "Category2", "Category3", "Category4", "Category5")).PrintOut , , 1



B.

1. Do you have the 5 category tabs?
2. Did I spell anything incorrectly? (I tested it with only two additional tabs and then rewrote that line)

There is some more work that needs to be done though.  When I did it it printed in portrait instead of landscape.

I've been playing with it; I actually have 12 Range names that I want to print but only identified 5 in the Macro.  Also, I use the other macro to distribute the new tabs before trying to print.  I still get the same error.  Spelling is correct.

B.

Here is what I have from the last sample that you gave in the previous post (question).

I currently have the section commented out to print each one on a separate page because I can't get the page setup function working properly.
Test-Case-for-generating-tabs-1-.xlsm

Pony10us,

The Print function works; however, I need to force a page break between the Category printouts; it's putting all of them on one page.  Is there a designator that forces a page break?

Much thanks,

B.

The part that I have commented out would print one per page however I can't seem to get the landscape and fit to page to work on that one.  I may get some time later today to work on it but the code is there if you want to play with it as well.

Bright01,

May I suggest that you continue this discussion with a new question regarding your print request.  That way, pony10us may get credit for his contributions here.

Thanks,
Glenn