Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

Save Excel 2007 as template without the data

I created a workbook from imported data and added pivot tables and charts to it.  I want to save the workbook as a macro enabled template without the data, but it only seems to have options to save or refresh data when opened.  If I can't save the template without the data, is there a macro I can use to clear all the data?  There are 7 worksheets of data and 5 worksheets with tables/charts that use the data.  I need to preserve the formatted worksheets and clear the raw data worksheets.
 It seems that this should be a standard feature, but I am not able to find it.
0
Ed_CLP
Asked:
Ed_CLP
  • 2
1 Solution
 
rspahitzCommented:
the idea of a template is to save the current setting and make it as a starting point for the next piece.  if you want to remove the data, then do that manually and save as a template.  every time you open the template it will create a new workbook without the data.  Excel treats everything in cells as data (text, formulas, formatting, etc) so how would it know what you want to keep and what you want to clear?

But yes, a macro could be CREATED to do that if you need it done a certain way or a different way each time based on known or requested criteria (such as a popup box to ask which sheets you'd like to clear.)

you said " it only seems to have options to save or refresh data when opened" Are you connected to an external data source?  maybe that's the problem.
0
 
Ed_CLPAuthor Commented:
The workbook I want to save as a template was created from another workbook.  I used VBA to copy several worksheets from one workbook to another.  I guess this created a link to the original workbook.
If you can provide a macro to clear all used cells on worksheets 1 to 7 and leave the rest untouched I'll up the question to 500 points.
Thanks for your help.
0
 
rspahitzCommented:
Clear all used cells?  How do you determine what is considered a used cell?  Is it simply Worksheet1, Ctrl+A, Delete?  or do you need to retain certain cells?

This will clear the contents of all sheets as described above:
 
Sub ClearAllCells()
'
' ClearAllCells Macro
'
    Dim objSheet As Worksheet
    For Each objSheet In ActiveWorkbook.Sheets
        objSheet.Select
        Cells.ClearContents
    Next
End Sub

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now