Deleting Tabs from Workbook

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.
Bright01Asked:
Who is Participating?
 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
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

0
 
pony10usConnect With a Mentor 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
0
 
pony10usCommented:
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.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Glenn RayExcel VBA DeveloperCommented:
Thanks.  I do like knowing that a subroutine completes correctly, even if it takes a split second!
0
 
Bright01Author 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.
0
 
Bright01Author Commented:
Glenn, Pony,

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

B.
0
 
pony10usCommented:
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.
0
 
Bright01Author 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
0
 
Rob HensonFinance AnalystCommented:
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
0
 
Bright01Author 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.
0
 
pony10usCommented:
I believe the proper method is the second option:

PrintOutRange("Category1,Category2,Category3")
0
 
pony10usCommented:
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
0
 
Bright01Author Commented:
Here's what I have but doesn't seem to work;

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

B.
0
 
pony10usCommented:
You are missing the commoa between each catagory

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

Sorry.
0
 
pony10usCommented:
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
0
 
Bright01Author 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.
0
 
pony10usCommented:
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.
0
 
Bright01Author 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.
0
 
pony10usCommented:
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
0
 
Bright01Author 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.
0
 
pony10usCommented:
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.
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.