Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an Excel template with 20 tabs (worksheets) and plenty of data in each sheet. When a user opens a copy of the template, he will only need to use one tab.

Is there a sneaky way to select that tab, or part of the contents of that tab and then delete all except selected? That way the used file size will be much reduced of the excess clutter.

There will be basic Excel users adjusting this file so the smaller and easier to manage the better.

share|improve this question
I suspect what you are after is some VBA, so perhaps this question would be better suited to StackOverflow. See for example ThisWorkbook.Close eg SO2282021 - though that particular question may be a bit more complicated than your requirement. – pnuts Dec 21 '12 at 11:02

It seems that the fastest way would be to copy/paste the selected section in a new blank sheet. When doing the paste, pick the option to get all formatting, formulas, etc. A new blank sheet is the minimal size. Removing cells from an existing sheet will still leave cached history and other effects.

share|improve this answer
Perhaps better yet, just copy the entire tab to a new (blank) workbook. – Scott Dec 20 '12 at 4:58
thanks for your ideas guys. I was hoping for a more automated method, as I said the end users don't really care or know how to best manipulate the excel, so I was hoping that there would be a code that code strip all but selected tab on save, or something as clever as that. – yesmaybe Dec 21 '12 at 2:46
Why deliver spreadsheets that contain more data than the user needs? Can't you do this simplification once before you give the end users the spreadsheet? Maybe someone can help you more if you describe your process. – jdh Dec 21 '12 at 3:32
We have a range of products/services which will be tailored for each customer. Open the product excel and it opens on the first 'menu' tab which lists all the products on subsequent tabs, approximately 20. Once on that tab, dates, prices etc can be adjusted and the workbook saved for that particular customer for future reference. I would like the other tabs (products) to be removed once one tab has been chosen. It isn't a big problem, just recently one staff was confused when accidently switching between tabs and inputting data on multiple tabs. – yesmaybe Dec 21 '12 at 3:40
It sounds using Excel's VBA (Visual Basic) would be a good solution. Perhaps if the first sheet could list some overview instructions and one or more VB controls (a dropdown list of possible situations/sheets) or an array of radio buttons, etc. So far this is just drag & drop to create. Then associate an event when the user uses one of the controls. At this point, the VBA code can delete the unused sheets (tabs) and any other unneeded data. The VBA code is really very simple, probably like 10 lines of code. – jdh Dec 22 '12 at 15:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .