Solved

Save Excel 2007 as template without the data

Posted on 2011-03-02
3
233 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now