Move a row from one sheet to another without the conditional fromatting

[Phily50]

Hello, Excel newbie here

I have adapted this little bit of vba code from somewhere I googled and it works very well:

Private Sub CCOk_Click()

Dim Answer As Integer
Dim NewSht As Worksheet
Dim NewRow As Long
Dim ThisRow As Long

Answer = MsgBox("You have selected to cancel " & UCase(ActiveCell.Value) & vbNewLine & vbNewLine & "This will move the row to Cancelled and remove ALL data from this page", vbExclamation + vbYesNo + vbDefaultButton2, "Are you sure?")

    If Answer = vbYes Then
        Set NewSht = Sheets("Cancelled")

            Application.EnableEvents = False
            Application.Calculation = xlCalculationManual
            ThisRow = ActiveCell.Row
                
                NewRow = NewSht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                Cells(ThisRow, "A").Copy NewSht.Cells(NewRow, "A")
                Cells(ThisRow, "B").Copy NewSht.Cells(NewRow, "B")
                Cells(ThisRow, "C").Copy NewSht.Cells(NewRow, "C")
                Cells(ThisRow, "D").Copy NewSht.Cells(NewRow, "D")
                Cells(ThisRow, "E").Copy NewSht.Cells(NewRow, "E")
                                  
                ActiveCell.EntireRow.Delete
           
                    Application.CutCopyMode = False
                    Application.EnableEvents = True
                    Application.Calculation = xlCalculationAutomatic
        Else
        'do nothing
    End If
    
frmCancelCHIS.Hide
End Sub

However, it copies the conditional formatting and then screws up the conditional formatting in the sheet it is copying to. Is there a way I can copy just the values as listed in the code and then paste them into the destination sheet so that it follows the rules as to where they are being copied to.

Hope this makes sense!

 

[GWteB]

Since you're only interested in the values, instead of copying a (source) range to another (target) range, you could assign the value property of the source range to the value property of the target range.
So code like this:

Cells(ThisRow, "A").Copy NewSht.Cells(NewRow, "A")

could look like this:

NewSht.Cells(NewRow, "A").Value = Cells(ThisRow, "A").Value

 

[Phily50]

Fantastic, works perfectly. Thanks!

Follow up question lol.....

That bit of code deletes the row that the data was in. In another sheet I have the following formulas:

=IF(SHEET2!A4="","",(INDEX(SHEET2!A4:J4,MATCH(TRUE,INDEX((SHEET2!A4:J4<>0),0),0))))

=IF(SHEET2!A4="","",(LOOKUP(2,1/(SHEET2!A4:I4<>""),SHEET2!A4:I4)))

=IF(SHEET2!A4="","",(LOOKUP(2,1/(SHEET2!A4:J4<>""),SHEET2!A4:J4)))

Which basically looks up the 1st and last empty cells in the specified ranges and pulls it into a list for use elsewhere. When I delete the row using that function above, I encounter the #REF error because the row no longer exists. Is there a way to resolve this by changing the formula?

 

[GWteB]

You are welcome and thanks for letting me know.

When I delete the row using that function above, I encounter the #REF error because the row no longer exists. Is there a way to resolve this

As this is an entirely different question, I think it's reasonable to ask you to start a new thread.