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.
Ed_CLPAsked:
Who is Participating?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.