im bewildered whats going wrong

[firexcel]

i have a macro to run which copies the =now() from another cell same page

this is the code

Sub Macro11()
'
' Macro11 Macro
'
'ActiveSheet.Unprotect "6477"    
   Range("m2").Select
Selection.Copy
Range("B" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("c" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell.FormulaR1C1 = "IN"
Range("d" & Rows.Count).End(xlUp).Offset(1).Select
'ActiveSheet.Protect "6477"


    
        
End Sub

the problem is the copied date is changed and wrong example the month and day swap around


how can I ensure when the macro runs the correct dd/mm/yyyy hh:mm is pasted

 

[RoryA]

I cannot see how that would happen if you have an =NOW() formula in M2. You don't need all that code though:

Sub Macro11()

'ActiveSheet.Unprotect "6477"    

With Range("B" & Rows.Count).End(xlUp).Offset(1)
   .Value = Range("M2").Value
   .Offset(, 1).Value = "IN"
   .Offset(, 2).Select
End With

'ActiveSheet.Protect "6477"
End Sub

 

[wideboydixon]

You probably need to copy the number format as well. The code you posted looks like a complicated way of doing this:

Public Sub Macro11()

Dim lastRow As Long

'ActiveSheet.Unprotect "6477"
lastRow = Cells(Rows.Count, "B").End(xlUp).Row + 1
Cells(lastRow, "B").Value = Range("M2").Value
Cells(lastRow, "B").NumberFormat = Range("M2").NumberFormat
Cells(lastRow, "C").FormulaR1C1 = "IN"
Cells(lastRow, "D").Select
'ActiveSheet.Protect "6477"

End Sub

WBD

 

[wideboydixon]

Rory's code is more succinct. Just maybe add in copying the numberformat as well as the value:

Sub Macro11()

'ActiveSheet.Unprotect "6477"    

With Range("B" & Rows.Count).End(xlUp).Offset(1)
   .Value = Range("M2").Value
   .NumberFormat = Range("M2").NumberFormat
   .Offset(, 1).Value = "IN"
   .Offset(, 2).Select
End With

'ActiveSheet.Protect "6477"

End Sub

WBD

 

[firexcel]

thanks guys - is it possible to have a corrupt sheet as when I use the code it works fine, when I change the time to a historical one the day months swap, then if I just double click on the cell and do nothing each time I do this it swaps automatically !!

 

[RoryA]

It sounds like your historic dates are being entered as text, or you have some other code running (e.g. a Worksheet_Change event).

 

[firexcel]

It sounds like your historic dates are being entered as text, or you have some other code running (e.g. a Worksheet_Change event).

yes yes yes yes

thanks rory my worksheet change event included the date column which is no need - all working as should now thanks all