Traer datos de otra columna con una macro y pegar como valores?

Prueba con esta macro

Sub copiarypegar()
filas = Range("z2").CurrentRegion.Rows.Count
With Range("ay2").Resize(filas, 1)
    .Formula = "=z2"
    .Value = .Value
End With
End Sub

Aquí otra macro para ti para considerar.

Sub PonerValores()
  Range("AY2:AY" & Range("Z" & Rows.Count).End(3).Row).Value = Range("Z2", Range("Z" & Rows.Count).End(3)).Value
End Sub