Copy and Paste Values as Number Format

Hello,

I am trying to write a Macro where it will use date and time (protected fields), and copy and paste values in number format (not general) for only fields that have a formula. I believe I am close, but I keep getting a debug error ~1004 on the third to last line (NumberFormat). Any help would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)

   Application.EnableEvents = False
   If Not Intersect(Target, Range("A:A")) Is Nothing Then
      If Target.Offset(0, 1).Locked = False Then
         ActiveSheet.Unprotect "isitontime"
         Target.Offset(0, 1).Value = Now
         Target.Offset(0, 1).Locked = True
         Target.Offset(0, 0).Locked = True
         ActiveSheet.Protect "isitontime"
      End If
      Dim r1 As Range, r2 As Range
   Set r1 = Range("C1:C1000000")
   Set r2 = Range("D1:D1000000")
   ActiveSheet.Unprotect "isitontime"
   r1.Copy
   r2.PasteSpecial Paste:=xlPasteValues
   ActiveSheet.Protect "isitontime"
   End If
   Application.EnableEvents = True
   Range("D1:D1000000").NumberFormat = "0"
   Range("A" & Cells(Rows.Count, "A").End(xlUp).Row + 1).Select
End Sub

Hi Daniel,

I think you're getting the error when the sheet is protected when you access the line:

 Range("D1:D1000000").NumberFormat = "0"

There does not seem to be an error when the sheet is NOT protected when you access the line.

I can't suggest a fix, because I'm not sure what you're trying to do.

Lewis