Pasting formats in without conditional formatting?
 

Is there a way in VB code to paste the values and formats (colours, borders
and number formats) from copied cells but not paste the conditional
formatting? I am using the following code:

Range(c, c.Offset(0, 17)).Copy
With Range(Sheets(strPSSheet).Cells(intWSPSRow, 1),
Sheets(strPSSheet).Cells(intWSPSRow, 18))
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteFormats

End With
Application.CutCopyMode = False

The problem is that my conditional format in the source range includes a
formula that in turn includes a name so that if I paste two or more ranges
from the source to the target the macro always stops with the message "A
formula or sheet you want to copy includes the name 'MyName' which already
exists on the destination worksheet. Do you want to use this version of the
name? ..." etc..

I guess an alternative would be to prevent the message from appearing. Is
this possible? If I could do this I could just delete the conditional
formatting after the paste.

TIA

TM

Pasting formats in without conditional formatting?
 

Try application.DisplayAlerts = False

turn them back on by setting it to true.
--
Regards,
Tom Ogilvy


"TishyMouse" wrote:

Is there a way in VB code to paste the values and formats (colours, borders
and number formats) from copied cells but not paste the conditional
formatting? I am using the following code:

Range(c, c.Offset(0, 17)).Copy
With Range(Sheets(strPSSheet).Cells(intWSPSRow, 1),
Sheets(strPSSheet).Cells(intWSPSRow, 18))
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteFormats

End With
Application.CutCopyMode = False

The problem is that my conditional format in the source range includes a
formula that in turn includes a name so that if I paste two or more ranges
from the source to the target the macro always stops with the message "A
formula or sheet you want to copy includes the name 'MyName' which already
exists on the destination worksheet. Do you want to use this version of the
name? ..." etc..

I guess an alternative would be to prevent the message from appearing. Is
this possible? If I could do this I could just delete the conditional
formatting after the paste.

TIA

TM

Pasting formats in without conditional formatting?
 

OK that works for me. Thanks, Tom. Interestingly (or not ;-)), Excel creates
a sheet-specific instance of the name the first time I cut & paste the
conditional formats, and retains this even when I subsequently delete the
conditional formatting using .FormatConditions.Delete. So in order to make
sure I have everything tidied up properly I have to delete this name at the
end of my paste operations. All done and it works fine now.

"Tom Ogilvy" wrote:

Try application.DisplayAlerts = False

turn them back on by setting it to true.
--
Regards,
Tom Ogilvy


"TishyMouse" wrote:

Is there a way in VB code to paste the values and formats (colours, borders
and number formats) from copied cells but not paste the conditional
formatting? I am using the following code:

Range(c, c.Offset(0, 17)).Copy
With Range(Sheets(strPSSheet).Cells(intWSPSRow, 1),
Sheets(strPSSheet).Cells(intWSPSRow, 18))
.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
.PasteSpecial Paste:=xlPasteFormats

End With
Application.CutCopyMode = False

The problem is that my conditional format in the source range includes a
formula that in turn includes a name so that if I paste two or more ranges
from the source to the target the macro always stops with the message "A
formula or sheet you want to copy includes the name 'MyName' which already
exists on the destination worksheet. Do you want to use this version of the
name? ..." etc..

I guess an alternative would be to prevent the message from appearing. Is
this possible? If I could do this I could just delete the conditional
formatting after the paste.

TIA

TM