VBA Scenario Copy/Paste

[Excellent_Mick]

Hello folks,

My limited VBA knowledge is letting me down at a critical moment.

I have 6 scenarios. These are controlled by a drop down list (rng_Option) and I want a macro to select each scenario and copy the results into another sheet.
The range to be copied is I62:M76 (rng_Results) and I want these results pasted into a summary page.

The issue is that the range to be pasted is 5 columns, so the macro has to copy the results then paste into the first area (lets say A62:E76), it then has to select the next scenario, copy the results and paste into the next 5 columns (F62:J76) and do this for all 6 scenarios.

I can't work out how to tell it to skip 5 columns before pasting each time...

Thanks in advance you great people.

 

[Fluff]

Hi & welcome to the board.
Something like

Sub chk()
Dim i As Long
For i = 1 To 30 Step 5
   Range("I62:M76").Copy Sheets("sheet1").Cells(62, i)
Next i
End Sub

 

[Excellent_Mick]

Thanks for the response.

The 'step' bit is definitely helpful but I'm still struggling to get it to work.
I need to select each of the 6 scenarios before copying the ranges - how would you build that in?

 

[Fluff]

Where does the drop down gets it's info from?

 

[Excellent_Mick]

From a row of input cells with scenario names in them (I18:N18)

 

[Fluff]

Maybe something like

Sub chk()
Dim i As Long
Dim Cl As Range
i = 1
For Each Cl In Range("I18:N18")
   Range("[COLOR=#ff0000]H18[/COLOR]").Value = Cl.Value
   Range("I62:M76").Copy Sheets("sheet1").Cells(62, i)
   i = i + 5
Next Cl
End Sub

Change the range in red to wherever your drop down is