Need expert help with VB code for copying pasting range values to column iteratively

[adhar1]

Hi,

I am looking for some help with developing VB code for the following steps for a financial model:

(1) inputting a number into a cell which defines a scenario of assumptions (there are 5 scenarios i.e. scenario 1,2,3,4,5)
(2) running a procedure to calculate the Interest during calculation (circular calculation)
(3) copying the generated values in a column to another predefined column
(4) inputting scenario 2 into cell defined at (1) and repeating step 2
(5) copying the generated values in a column to another predefined column which is in the next column as the destination column in 3
(6) repeat this for scenarios 1,2,3,4,5

Please see my code below. I am looking for what is wrong with it. Right now running this code results in only one scenario result (scenario 2 which is the active scenario right now) being copied into all the 5 target columns.
_______________________________
Sub scheck()


Dim Scen As Integer

Dim i As Integer

Scen = Worksheets("Scenarios").Range("I11")


For Scen = 1 To 5

Do Until Worksheets("Capex Funding").Range("F77").Value = Worksheets("Capex Funding").Range("F34").Value

Worksheets("Capex Funding").Range("IDC_COPY").Copy
Worksheets("Capex Funding").Range("IDC_PASTE").PasteSpecial xlPasteValues

Loop

i = Scen

Select Case i
Case 1
Worksheets("Scenarios").Range("I37:I51").Copy
Worksheets("Scenarios").Range("J37:J51").PasteSpecial xlValues
Case 2
Worksheets("Scenarios").Range("I37:I51").Copy
Worksheets("Scenarios").Range("K37:K51").PasteSpecial xlValues
Case 3
Worksheets("Scenarios").Range("I37:I51").Copy
Worksheets("Scenarios").Range("L37:L51").PasteSpecial xlValues
Case 4
Worksheets("Scenarios").Range("I37:I51").Copy
Worksheets("Scenarios").Range("M37:M51").PasteSpecial xlValues
Case 5
Worksheets("Scenarios").Range("I37:I51").Copy
Worksheets("Scenarios").Range("N37:N51").PasteSpecial xlValues
End Select
Next Scen

End Sub
____________________________________

looking for some help. thanks!

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

The copy and paste operations in your case statement all copy the same cells into 5 different columns. Which part of the program puts different values into Range("I37:I51") ?

The clue might be in the IDC_COPY and IDC_PASTE defined names but we don't have enough information to tell.

 

[adhar1]

Hi,

Thanks for your response.

The following section of the code changes the values in I37:I51.

For each value of Scen, the IDC portion in green is run. each value of scen gives rise to different model assumptions (in another sheet). The model IDC code is run and the results are generated in I37:I51. This repeated for Scen = 1 to 5.

Hope I am clear.
____________________________________
For Scen = 1 to 5

Do Until Worksheets("Capex Funding").Range("F77").Value = Worksheets("Capex Funding").Range("F34").Value

Worksheets("Capex Funding").Range("IDC_COPY").Copy
Worksheets("Capex Funding").Range("IDC_PASTE").PasteSpecial xlPasteValues

Loop

 

[RickXL]

The problem might be that nothing is, in fact, changing I37:I51. That would give the symptoms you describe.

The code you point to seems to copy a fixed range every time. Why do you think I37:I51 is changing?