Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formulas between sheets and keeping original ref | Excel Worksheet Functions | |||
Copying formulas between workbooks | Excel Discussion (Misc queries) | |||
Copying an Excel sheet keeping the formulas in the copy linked to | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) | |||
Copying formulas to other cells. Keeping references w/o $ sign. | Excel Discussion (Misc queries) |