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

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.

Copying values and formatting but not formulae to new worksheetwith VBA
 

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.

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

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

--
David

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

Thanks, that solves it exactly.

David wrote:
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

--
David