Dovresti prima usare il metodo activate sul foglio e poi selezionare il range ma se devi fare un semplice copia incolla puoi anche fare una cosa del genere:

codice:
Sheets(1).Range("a5").Copy Destination:=Sheets(2).Range("a1")