We help IT Professionals succeed at work.

Deleting Tabs from Workbook

Bright01
Bright01 asked
on
I have a great Worksheet that EE Professionals Pony and Glenn (see original Question) wrote and posted; it creates Tabs from a Worksheet and populates each tab appropriately.  What I need now are several lines of a macro that auto deletes all of the Tabs so a user can "start all over".

That's it!

Thank you in advance,

B.
Comment
Watch Question

Steven CarnahanAssistant Vice President\Network Manager
Commented:
This will delete worksheets without prompting.

Application.DisplayAlerts = False               '  <---- turns off "are you sure" alert
Worksheets("MySheet").Delete                  '  <---- deltes names sheet
                                                                    '  <----  repeat for each worksheet name
Application.DisplayAlerts = True                 '  <---- turns "are you sure" alert back on
Excel VBA Developer
Top Expert 2014
Commented:
Glad to help in the previous question.

I assume you want to delete all worksheets EXCEPT the first one.  Here's the code.

-Glenn

Sub Clear_Sheets()
    Application.DisplayAlerts = False
    For x = ActiveWorkbook.Sheets.Count To 2 Step -1
        Sheets(x).Delete
    Next x
    Application.DisplayAlerts = True
    MsgBox "Sheets Removed."
End Sub

Open in new window

Steven CarnahanAssistant Vice President\Network Manager

Commented:
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.
Glenn RayExcel VBA Developer
Top Expert 2014

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

Author

Commented:

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.

Author

Commented:
Glenn, Pony,

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

B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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.

Author

Commented:
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
Rob HensonFinance Analyst

Commented:
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

Open in new window


Thanks
Rob H

Author

Commented:
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.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
I believe the proper method is the second option:

PrintOutRange("Category1,Category2,Category3")
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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

Author

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

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

B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
You are missing the commoa between each catagory

Author

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

Sorry.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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

Author

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

on this line:

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



B.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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.

Author

Commented:
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.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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

Author

Commented:
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.
Steven CarnahanAssistant Vice President\Network Manager

Commented:
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.
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
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