Remove Conditional Formatting

[sandirutland]

I've managed to muddle my way through filtering data from multiple worksheets and copying it to a new worksheet.

Problem: When the data is copied, so is the existing conditional formatting. I need a VBA code to automatically remove the conditional formatting from the new worksheet.

Also, is there a VBA code that will automatically sort a column?

I've searched and searched and can't find exactly what I'm looking for. Maybe it can't be done?

Thanks for all your help!!!

Sandi

[Bob Phillips]

rng.FormatConditions.Delete

[sandirutland]

I guess I wasn't clear. I need the new DestSh to clear formatting, not the source sheet. I can't get it to work in the following code. Any suggestions?

'Add a worksheet with the name "DUE"
Set DestSh = ActiveWorkbook.Worksheets.Add
DestSh.Name = "DUE"

'Copy/paste the visible data to the new worksheet
My_Range.Parent.AutoFilter.Range.Copy
With DestSh.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

'Close AutoFilter
My_Range.Parent.AutoFilterMode = False

[Bob Phillips]

Just change rng in my code to that target range.

[sandirutland]

Sorry, I'm confused.
I've tried putting it in a few different areas and I can't figure it out. I'm not a VBA person, but I can manage to google answers and sometimes combine them to work for my needs.
When it comes to writing code, I'm a total dummy.

[Bob Phillips]

Try adding



Selection.FormatConditions.Delete

[sandirutland]

Where do I add it and how does it know what "selection" i want the format conditions deleted from? Again, I appreciate your help. I know it must be frustrating working with people like me. :)

[lynnnow]

That line of code would be put like so (though it is untested):

'Copy/paste the visible data to the new worksheet
My_Range.Parent.AutoFilter.Range.Copy
Application.CutCopyMode = False
With DestSh.Range("A1")
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
.Select
.FormatConditions.Delete
End With

[Bob Phillips]

It will know, because after you paste, the pasted area is selected.