Solved

Save Excel 2007 as template without the data

Posted on 2011-03-02
3
240 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a serious pitfall that can happen when deleting shapes using VBA.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

688 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