View Full Version : Remove Conditional Formatting
sandirutland
08-25-2010, 08:49 AM
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
08-25-2010, 08:53 AM
rng.FormatConditions.Delete
sandirutland
08-25-2010, 09:08 AM
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
08-25-2010, 09:45 AM
Just change rng in my code to that target range.
sandirutland
08-25-2010, 09:49 AM
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
08-25-2010, 09:51 AM
Try adding
Selection.FormatConditions.Delete
sandirutland
08-25-2010, 09:56 AM
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
08-25-2010, 10:01 AM
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
08-25-2010, 10:35 AM
It will know, because after you paste, the pasted area is selected.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.