Copying values and formatting but not formulae to new worksheet with VBA

[ajkessel]

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.

 

[Rowan Drummond]

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

Sub cpy()
Dim cell As Range
Sheets(1).Copy before:=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

 

[David]

Rowan Drummond wrote

For Each cell In Sheets(1).UsedRange
cell.Value = cell.Value
Next cell

FWIW, my experience has been I can write this as:
Sheets(1).UsedRange.Value = Sheets(1).UsedRange.Value

 

[ajkessel]

Thanks, that solves it exactly.

Rowan Drummond wrote


FWIW, my experience has been I can write this as:
Sheets(1).UsedRange.Value = Sheets(1).UsedRange.Value