Solved

Save Excel 2007 as template without the data

Posted on 2011-03-02
3
237 Views
Last Modified: 2012-05-11
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
Comment
Question by:Ed_CLP
  • 2
3 Comments
 
LVL 22

Expert Comment

by:rspahitz
ID: 35018670
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
 

Author Comment

by:Ed_CLP
ID: 35018732
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
 
LVL 22

Accepted Solution

by:
rspahitz earned 250 total points
ID: 35018837
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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question