changing date format

[bdt]

I'd like to change the date format when copying data to another sheet. currently the format is **.**.** but need to change it to **/**/**. I've tried the obvious formatting the destination cells to the required format but I get #VALUE!
I have the code, but get an Error 400

    'copy overtime sunday
With Sheets("ABACUS")
    WKend = .Range("M2").Value2
    arr = .Range("AI21:AI33").Value
End With

With Sheets("OVERTIME")
    ' last used row in column B plus 1
   
    writerow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    .Range("A2") = Format(Date, "dd/mm/yy")
    .Range("A" & writerow) = WKend
    .Range("B" & writerow).Resize(, UBound(arr)).Value = Application.Transpose(arr)
End With

Any help appreciated, thanks

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[HighAndWilder]

I'd like to change the date format when copying data to another sheet. currently the format is **.**.** but need to change it to **/**/**. I've tried the obvious formatting the destination cells to the required format but I get #VALUE!
I have the code, but get an Error 400
'copy overtime sunday
With Sheets("ABACUS")
WKend = .Range("M2").Value2
arr = .Range("AI21:AI33").Value
End With

With Sheets("OVERTIME")
' last used row in column B plus 1

writerow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
.Range("A2") = Format(Date, "dd/mm/yy")
.Range("A" & writerow) = WKend
.Range("B" & writerow).Resize(, UBound(arr)).Value = Application.Transpose(arr)
End With

Any help appreciated, thanks

The FORMAT function returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

Write the value, today's date in this case, to the cell and then change the NumberFormat of the cell to "dd/mm/yy".

 

[NoSparks]

@bdt, it appears that you come back and view your threads but do not acknowledge or respond to things.
If there is something you don't follow or understand, just ask.
We were all VBA beginners at one time and are willing to help.

Altering what you have to this will do what you're after

Sub testing()
    
    Dim WKend As Date
    Dim writerow As Long
    Dim arr As Variant
    
    'copy overtime sunday
With Sheets("ABACUS")
    WKend = .Range("M2").Value2
    arr = .Range("AI21:AI33").Value
End With

With Sheets("OVERTIME")
    ' last used row in column plus 1
    writerow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
    ' write data to column A
    .Range("A" & writerow) = WKend
    ' format that data as desired
    .Range("A" & writerow).NumberFormat = "dd/mm/yyyy"
    ' write the array of data to same row starting at column B
    .Range("B" & writerow).Resize(, UBound(arr)).Value = Application.Transpose(arr)
End With

End Sub

 

[bdt]

NoSparks, I appreciate your comments and your alteration to the code. This has done what I required. Many thanks.
I have a couple of other problems which I will put in new threads.

 

[NoSparks]

Glad to be able to help and thanks for the feedback.