Copying Workbooks and keeping the formulas only?
 

I have a workbook that contains almost 50 worksheets that has all the sales,
quota, regions, etc for this year. I need to use this workbook for Sept 08 -
Sept 09, but do not want all of the numbers that have been entered. I only
need the formulas for all of the calculations and I will enter the other
information (Like Quotas, etc) that needs to be entered.

How do I do this? It only allows me copy all of the worksheets into another
workbook, but copies everything. I just need the formatting and the
formulas...

Thanks in advance.

Copying Workbooks and keeping the formulas only?
 

Well, for 50 sheets this would be tedious.

work with a copy of your workbook.
Press the F5 key, then choose Special.
Choose Constants and uncheck anything you want to keep, text for instance.
If you want to eliminate JUST the numbers, uncehck everything but #s. Click
on OK

Now press the Delete key

Repeat for subsequent worksheets

You could record a macro and re-run it on each sheet


"John" wrote:

I have a workbook that contains almost 50 worksheets that has all the sales,
quota, regions, etc for this year. I need to use this workbook for Sept 08 -
Sept 09, but do not want all of the numbers that have been entered. I only
need the formulas for all of the calculations and I will enter the other
information (Like Quotas, etc) that needs to be entered.

How do I do this? It only allows me copy all of the worksheets into another
workbook, but copies everything. I just need the formatting and the
formulas...

Thanks in advance.

Copying Workbooks and keeping the formulas only?
 

Here is the Macro suggested by Duke. It will loop through all sheets and
delete everything except formulae.
It will fail if any worksheet is blank or has only formulae;

Sub Macro1()
For Each wsheet In Worksheets
wsheet.Activate
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
wsheet.Range("A1").Select
Next wsheet
End Sub

"Duke Carey" wrote:

Well, for 50 sheets this would be tedious.

work with a copy of your workbook.
Press the F5 key, then choose Special.
Choose Constants and uncheck anything you want to keep, text for instance.
If you want to eliminate JUST the numbers, uncehck everything but #s. Click
on OK

Now press the Delete key

Repeat for subsequent worksheets

You could record a macro and re-run it on each sheet


"John" wrote:

I have a workbook that contains almost 50 worksheets that has all the sales,
quota, regions, etc for this year. I need to use this workbook for Sept 08 -
Sept 09, but do not want all of the numbers that have been entered. I only
need the formulas for all of the calculations and I will enter the other
information (Like Quotas, etc) that needs to be entered.

How do I do this? It only allows me copy all of the worksheets into another
workbook, but copies everything. I just need the formatting and the
formulas...

Thanks in advance.

Copying Workbooks and keeping the formulas only?
 

This modification should not fail for blank worksheets or worksheets with
only formulas...

Sub Macro1()
On Error Resume Next
For Each wsheet In Worksheets
wsheet.Activate
Selection.SpecialCells(xlCellTypeConstants, 23).Select
If Err.Number = 0 Then
Selection.ClearContents
wsheet.Range("A1").Select
End If
Next wsheet
End Sub

--
Rick (MVP - Excel)


"Sheeloo" wrote in message
...
Here is the Macro suggested by Duke. It will loop through all sheets and
delete everything except formulae.
It will fail if any worksheet is blank or has only formulae;

Sub Macro1()
For Each wsheet In Worksheets
wsheet.Activate
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Selection.ClearContents
wsheet.Range("A1").Select
Next wsheet
End Sub

"Duke Carey" wrote:

Well, for 50 sheets this would be tedious.

work with a copy of your workbook.
Press the F5 key, then choose Special.
Choose Constants and uncheck anything you want to keep, text for
instance.
If you want to eliminate JUST the numbers, uncehck everything but #s.
Click
on OK

Now press the Delete key

Repeat for subsequent worksheets

You could record a macro and re-run it on each sheet


"John" wrote:

I have a workbook that contains almost 50 worksheets that has all the
sales,
quota, regions, etc for this year. I need to use this workbook for
Sept 08 -
Sept 09, but do not want all of the numbers that have been entered. I
only
need the formulas for all of the calculations and I will enter the
other
information (Like Quotas, etc) that needs to be entered.

How do I do this? It only allows me copy all of the worksheets into
another
workbook, but copies everything. I just need the formatting and the
formulas...

Thanks in advance.