Se usi il riferimento alle proprietà del foglio di lavoro non ti cambia, ogni volta il foglio attivo.

Prova questo ciclo (ovviament Foglio1 e Foglio2 vanno cambiati a tuo uso):
codice:
Dim row As Double
For row = 1 To Foglio1.Rows.Count
     Foglio2.Rows(row).Value = Foglio1.Rows(row).Value
Next row
P.S. In realtà il ciclo risulta molto lento perchè copia le righe di tutto il foglio di lavoro. Vedi tu come limitare il ciclo (al limite chiedi).