thanks for your reaction. but what if only one column in the range needs to be reformatted like this? Is there an easy way to do this?

Or should i do it after i pasted the range with a separate line of code like:
Column(A:A).NumberFormat = "ddmmyyyy"