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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steven CarnahanNetwork ManagerCommented:
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
Glenn RayExcel 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steven CarnahanNetwork ManagerCommented:
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
PMI ACP® Project Management

Prepare for the PMI Agile Certified Practitioner (PMI-ACP)® exam, which formally recognizes your knowledge of agile principles and your skill with agile techniques.

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
Steven CarnahanNetwork ManagerCommented:
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
Steven CarnahanNetwork ManagerCommented:
I believe the proper method is the second option:

PrintOutRange("Category1,Category2,Category3")
0
Steven CarnahanNetwork ManagerCommented:
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
Steven CarnahanNetwork ManagerCommented:
You are missing the commoa between each catagory

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

Sorry.
0
Steven CarnahanNetwork ManagerCommented:
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
Steven CarnahanNetwork ManagerCommented:
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
Steven CarnahanNetwork ManagerCommented:
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
Steven CarnahanNetwork ManagerCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.