copy a worksheet - but not the value (text)

[Guest]

I have a worksheet that has all the formulas and formating I need, I close it
out and start a new worksheet the end of the month.

What I want to do is make a copy of that worksheet WITHOUT copying the data,
Just the format and formulas.

(Don't want the names and addresses, or the amounts etc...)
I insert a work sheet, I copy the old one then when I go to paste, but the
options I want - Paste all EXCEPT value are not available.
Using the other choices Paste all the data.

When I click on just formulas - I still get all the data,

 

[Guest]

Buddy

A non programming method would be to delete the data and save the workbook
as a template (.xlt) file e.g. SalesTemplate.xlt and open this up at the
beginning of the month.

Save the template as say JanSales05.xls and enter work normally.

Peter Atherton

 

[Leith Ross]

Hello Buddy,

Here is macro that automatically adds a New Worksheet to the Workbook
and copies only the Cell Formats and Formulas that have been used on
the the Active Worksheet. No need to select the range to be copied.
Copy and Paste this code into a VBA standard Module. You can run the
macro by pressing the Keys *ALT and F8* to bring display the Macro
List. Select "CopyWorksheet" and click Run.


Code:
--------------------
Public Sub CopyWorksheet()
Dim UsedRange As Range
Dim NewCell As Range
Dim NewWks As Worksheet

ActiveWorkbook.Worksheets.Add

Set NewWks = Worksheets(Worksheets.Count)
Set UsedRange = ActiveSheet.UsedRange

For Each Cell In UsedRange
With Cell
.Copy
Set NewCell = NewWks.Range(.Address)
End With

With NewCell
.PasteSpecial (xlPasteFormats)
.PasteSpecial (xlPasteFormulas)
If Not Cell.HasFormula Then .Value = ""
End With
Next Cell

Application.CutCopyMode = False

End Sub