Copy, Paste, Calculate loop

[tabasco4me]

Hello all. First post here --- Hope you can help.

- I have a model with 100 scenarios, each scenario is 3 consecutive cells (variables) in a row. Total area for the scenarios is thus: [Sheet 1, A1:C100]

- The first scenario [Sheet 1, A1:C1] needs to be copied, and pasted into [Sheet 2, A1:C3]. Workbook should automatically calculate then. Result cell [Sheet2, D1] should then be copied, and pasted back into [Sheet1, D1].

- Next scenario [Sheet1, A2:C2] should be copied and pasted as above, the result then copied, and pasted in [Sheet 1, D2].

Upon completion of the last (100th) scenario , the process ends.

Thank you for any help!

 

[VoG]

Hi. try this

Sub tobasco()
Dim i As Long
With Sheets("Sheet1")
    For i = 1 To 100
        .Range("A" & i).Resize(, 3).Copy Destination:=Sheets("Sheet2").Range("A1")
        Sheets("Sheet2").Range("D1").Copy Destination:=.Range("D" & i)
    Next i
End With
End Sub

 

[tabasco4me]

Thanks, Peter. Works great --- You saved the day.

Sean