Currently I am using this construct to copy and paste a region so that
it contains no more formulas (the result is to be edited in ways that
would break the formulas, as in deleting some columns and reordering):

source.Copy
dest.PasteSpecial xlPasteColumnWidths
dest.PasteSpecial xlPasteValuesAndNumberFormats
dest.PasteSpecial xlPasteFormats

First question: is it possible to "paste special" all three types at
once?

Second question: xlPasteFormats pastes the conditional formats with
formulas. I want the conditional formats to "collapse" too, that is,
keep the formatting even if the cells are changed.

(Actually, what I have is:

- in A2 and below, there is a cell =IF(B2 < TODAY() - 90, "{old}", "")
- in B2, I set a conditional format to give the cell a red background if
B2 is < TODAY() - 90

However, I consider it unclean and only want the comparison coded in
once, that is, have a conditional format in B2 that checks if A2 is
empty or not, but as I want to delete the column A in the output, this
format would break)

#1. I don't think so. You have to do each separately (like you did).
#2. I don't think so. If you know what the conditional formatting rules are,
you could remove the CF and use code to change the formatting following those
rules and formatting specifications.

But if you don't know the rules, you could use Chip Pearson's technique to look
at the CF rules:
http://www.cpearson.com/excel/CFColors.htm

When I want to do this kind of thing (manually--not through code), I'll copy the
range into MSWord and then copy|Paste from there.



Marcus Schöneborn wrote:
>
> Currently I am using this construct to copy and paste a region so that
> it contains no more formulas (the result is to be edited in ways that
> would break the formulas, as in deleting some columns and reordering):
>
> source.Copy
> dest.PasteSpecial xlPasteColumnWidths
> dest.PasteSpecial xlPasteValuesAndNumberFormats
> dest.PasteSpecial xlPasteFormats
>
> First question: is it possible to "paste special" all three types at
> once?
>
> Second question: xlPasteFormats pastes the conditional formats with
> formulas. I want the conditional formats to "collapse" too, that is,
> keep the formatting even if the cells are changed.
>
> (Actually, what I have is:
>
> - in A2 and below, there is a cell =IF(B2 < TODAY() - 90, "{old}", "")
> - in B2, I set a conditional format to give the cell a red background if
> B2 is < TODAY() - 90
>
> However, I consider it unclean and only want the comparison coded in
> once, that is, have a conditional format in B2 that checks if A2 is
> empty or not, but as I want to delete the column A in the output, this
> format would break)

--

Dave Peterson