Se você estiver interessado em conseguir isso sem usar o Macro, precisará inserir seu valor de intervalo como uma fórmula de matriz. Para entrar como uma fórmula de matriz,
- Selecione o intervalo de destino com o mesmo tamanho do intervalo de origem
- Insira a fórmula
- Pressione Control + Shift + Enter
Consulte a documentação da Microsoft Introdução de fórmulas de matriz no Excel