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
--------------------


--
Leith Ross


------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=380414