Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pasting doesn't trigger conditional formatting | Excel Discussion (Misc queries) | |||
Pasting into a cell with conditional formatting | Excel Discussion (Misc queries) | |||
Conditional formatting - mutlple formats | Excel Worksheet Functions | |||
Paste Special Formats Generated by Conditional Formatting | Excel Discussion (Misc queries) | |||
How do I do conditional formatting on number formats not patterns. | Excel Discussion (Misc queries) |