Hello, Excel newbie here
I have adapted this little bit of vba code from somewhere I googled and it works very well:
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!
I have adapted this little bit of vba code from somewhere I googled and it works very well:
VBA Code:
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!