Conditional Formatting

[Excel Help!]

I have created a macro to copy data from a worksheet; create a new worksheet
and paste the data. However, the new worksheet has conditional formatting;
when I run the macro it deletes the worksheet along with the conditional
formatting. Is there a way to append the new data and maintain the
conditional formatting in the new worksheet so I don’t have to re-enter the
conditional formatting? Thanks in advance for any help.

 

[jlclyde]

I have created a macro to copy data from a worksheet; create a new worksheet
and paste the data.  However, the new worksheet has conditional formatting;
when I run the macro it deletes the worksheet along with the conditional
formatting.  Is there a way to append the new data and maintain the
conditional formatting in the new worksheet so I don’t have to re-enter the
conditional formatting? Thanks in advance for any help.

Could you just pasteSpecial(XlVlaues)? that way you keep the
formatting.

Jay

 

[Excel Help!]

I'm using it. The problem is I'm creating a new worksheet each time the
macro runs. Should I find a way to have the macro use the same worksheet
each time; delete the data and paste? Thanks for your help.

 

[Gord Dibben]

Currently you copy data from a worksheet.............right?

The you create a new worksheet via code.........right?

That worksheet has conditional formatting...............right?

How did it get the CF?

Did you add the CF through your code when you created the new worksheet?

Maybe add the CF after you have pasted the copied data?

How does your code "delete the worksheet"?

Time to post the code, I think.


Gord Dibben MS Excel MVP

 

[Excel Help!]

Here it goes.... Thanks for any inputs. I'm not a code writer.

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set WS = Sheets("AF") '<<< Change

Set rng = WS.Range("A1:J" & Rows.Count)


WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
Sheets("25IS").Delete
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=4, Criteria1:="=USA-560", Operator:=xlOr,
Criteria2:="=SCIF-0000000075"

Set WSNew = Worksheets.Add
WSNew.Name = "25IS"

WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
.Select
End With

WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With



End Sub

 

[Gord Dibben]

Rather than delete the copy-to sheet then add a new sheet, you could just
clearcontents before copying the new values.

This was tested on a workbook with the changes seen.

Adjust to your needs.

Sub Copy_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Set WS = Sheets("Day") '<<< Change

Set rng = WS.Range("A1:J" & Rows.Count)
Set WSNew = Worksheets("Sheet6")

WS.AutoFilterMode = False
On Error Resume Next
Application.DisplayAlerts = False
WSNew.Cells.ClearContents
Application.DisplayAlerts = True
On Error GoTo 0

rng.AutoFilter Field:=4, Criteria1:="2Off", Operator:=xlOr, _
Criteria2:="2Night"

WS.AutoFilter.Range.Copy
With WSNew.Range("A1")
' Paste:=8 will copy the columnwidth in Excel 2000 and higher
.PasteSpecial Paste:=8
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

WS.AutoFilterMode = False

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub


Gord

 

[Excel Help!]

For some reason it adjusted the format of the data (header), but it works.
My last question: Is it possible to have the macro prompt the user to enter
the search Criteria data. This way I would not have to write the same code
10 times to match each user's request? Thanks again for the assistances.