5

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.

0

1 Answer 1

7

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.

5
  • 1
    Perhaps better yet, just copy the entire tab to a new (blank) workbook. Dec 20, 2012 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, 2012 at 2:46
  • 1
    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, 2012 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, 2012 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, 2012 at 15:58

You must log in to answer this question.

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