One way: copy entire sheet and then set formulae to values

Sub cpy()
Dim cell As Range
Sheets(1).Copy befo=Sheets(1)
For Each cell In Sheets(1).UsedRange
cell.Value = cell.Value
Next cell
Sheets(1).Name = "NewSheet"
End Sub

Hope this helps
Rowan

wrote:
I want to select a subset of cells from a complicated worksheet and
copy just the values and formatting from those cells to a new worksheet
programatically. The cells have conditional formatting, which I would
like to have come over as well. I would also like the new worksheet to
be formatted (column width, cell height, etc.) exactly as the source
worksheet.

Is there any way to do this? I know how to create a new empty
worksheet programatically, and I've experimented with various ways of
copying cells, and I can get the values to come over with, e.g.:

Worksheets(5).Range("A" & 1 & ":H" & LastRow - FirstRow).Value =
Worksheets(4).Range("A" & FirstRow & ":H" & LastRow).Value

But I can't figure out how to get the conditional formatting, cell
widths and heights, etc., to come over to the new worksheet.

Any tips would be much appreciated.