Creating a Small Archiveable File from a Large Macro-Enabled Template

  • Hello,


    I have developed a rather large estimating template in Excel 2010 that has buttons, VBA, file linking, conditional formatting, formulas, data validation, hidden tabs, named ranges, the whole 9 yards. My target with this request is to have a button that the end-user can click that will save a 3-tab, values-and-formatting-only xlsx file that the end user specifies the name of. I've basically created an estimating program using Excel (there's better/other applications for this, I know - limited company resources) and I'd like when the user saves their generated estimate, it's only a very small, "dumb" file, not a massive multi MB file. The large template file contains an instructions tab followed by 2 hidden data tabs referenced throughout the workbook, followed by a general input tab followed by multiple specific input tabs. The last 2 tabs are a bill of materials/shop labor tab and then a field labor/details tab; the 3 tabs I would like to save are the general input tab, the shop labor/BOM tab, and the field labor/details tab. Since this template has VBA code, file linking, formulas, etc., I would like to retain only a different, defined range of cells on the 3 tabs, the displayed values in each cell of the defined range on those 3 tabs, and the displayed cell formatting on those tabs; it needs to be a compact file, "look pretty", and "be dumb". Due to sensitivity of information in the template, I can not post the template here and I do need to save the generated estimates as excel files for other in-house data analysis functions. Hopefully this request makes sense and is possible?


    Thank you for your time

  • So what do you want tod oexactly?


    1. Convert Formulas to Values

    2. Remove all unnecessary sheets


    Try this, change the three sheet names as necessary


  • Hi Roy,


    Thank you for the quick reply. I've tried your code as instructed and am getting an error (see below).


    Step 0, the user clicks an ActiveX Command Button that I'll call, let's say, "Create Estimate". Even though I'm new to coding and still have a lot to learn, I can handle the button creation and assigning code to that button. That button click I would like to initiate the following:

    1. Create a new xlsx file with a save-as dialog box where users can specify the name and location of the file.
    2. The newly created xlsx file has all formulas converted to values.
    3. The newly created xlsx file has unnecessary sheets removed.
    4. The newly created xlsx file has all vba code, conditional formatting, and named ranges removed.
    5. bonus: there are Command Buttons and text boxes on the 3 tabs I am keeping that I would like to have either hidden or deleted in the newly created xlsx file.

    The 2nd half of the code makes sense in regards to keeping the 3 tabs that I want, but I think I'm missing something with how the code in the first half is supposed to work. I'm trying not to be one of those users that just blindly copy-pastes code without at least trying to understand how it works.

  • So what do you want tod oexactly?


    1. Convert Formulas to Values

    2. Remove all unnecessary sheets


    Try this, change the three sheet names as necessary


  • Sorry, I put the error notation in the quote of the #5 post; I'm getting a run-time 1004: Method 'Select of object 'Sheets' failed error at line 6 of your latest code: Worksheets.Select


    The only controls on the sheet are ActiveX Command Buttons that reset the user inputs on those sheets to default values; those default values are stored in the sheet-specific code.

  • see if this is any better


  • Adding a "Dim shp as Shape" line fixed that error, but I'm still running into the same 1004 error at the Worksheets.Select line. Could the fact that there are hidden sheets in the template have something to do with this?


  • Sorry, I forgot to add the declaration.


    This amendment should take care of the hidden sheets


  • Thanks Roy! One issue with the resultant .xlsx file is that the formulas on the last 2 tabs are still present; the code seems like it needs to be in a different order? I've tried rearranging, but keep getting different errors depending on how I rearrange it.

  • Try this


  • Bug somewhat fixed in that I don't get any coding errors; I removed "oWs." from that line. However, the last 2 tabs of the resultant .xlsx file still retain the formulas. I'm also getting #Ref! errors in the resultant .xlsx file for those last 2 tabs when I run the code below. So I'm wondering if the sheets need to be processed in a specific order in terms of converting the formulas to values? I ask this because the last 2 tabs of the large .xltm are almost entirely dependent on the inputs/values of all the preceding tabs.


  • So after much tinkering with both Roy's work here and this little gem that I found, I arrived at the code below that does the following when executed:

    1. Creates a new xlsx workbook using the save-as dialog box.
    2. The new workbook is a defined set of tabs from the original workbook.
    3. The new workbook is values only:
      1. All hard-code formulas are removed, all data validation is removed, all named ranges are removed, all hyperlinks are removed, all external links are removed.
      2. In my specific case, I deleted Command Buttons and Toggle Buttons as I had a group of pictures (think company logo in a letterhead) that I wanted to keep.

    Many thanks, Roy!