Loop validation, copy output from same range and paste in different ranges

[cyenwong]

I'm trying to loop through 10 scenarios via a validation list. On each scenario the calculation engine provides an output in a fixed range. The output needs to be copied / pasted onto a dashboard sheet going down the row with fixed distance (so the dashboard sheet will have 10 sets of output).

The validation list, output and dashboard are in 3 different sheets

I tried the following VBA code, however on each run the output seems to be very different. I tried F8, the validation list looping is working, output is also changing correctly. But the pasting bit seems to differ every time I run, although some random stuff that gets copied are being pasted into the correct range.

Appreciate if someone can tell me what's wrong, thanks!

Sub LoopThroughScenarioList_And_PasteScenarioOutput()

Dim rng As Range
Dim dataValidationArray As Variant
Dim i As Integer 'used for # of scenario
Dim j As Integer 'used for # of output pasting
Dim rows As Integer

'Set the cell which contains the Scenario validation list 
Set rng = Sheets("LC model").Range("AC8") ':AJ8

On Error Resume Next 'in case there is error

'Create an array from our Data Validation formula so it knows the boundary of the loop
rows = Range(Replace(rng.Validation.Formula1, "=", "")).rows.Count
ReDim dataValidationArray(1 To rows)

For i = 1 To rows
    dataValidationArray(i) = _
        Range(Replace(rng.Validation.Formula1, "=", "")).Cells(i, 1)
Next i

'Loop through all the scenarios in array defined above
For i = LBound(dataValidationArray) To UBound(dataValidationArray)

    'Change the value in the scenario selection cell
    rng.Value = dataValidationArray(i)
    
    'Force the sheet to recalculate
    Application.Calculate
    
    'Copy the output
    Sheets("Macro to copy").Range("G6:H8").Select
    Selection.Copy
    
    'Paste the output, starting from Scenario 1 location
    'Sheets("Macro to copy").Range("M6").Offset(j, 0).Select 'j = 0 right now
    Sheets("Baseline & Scenario Output").Select
    Range("Q24").Offset(j, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
            
    'Update value j so next paste is on Scenario 2 location. 16 is the distance between each scenario output
    j = j + 16
      
Next i

End Sub

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at:

https://stackoverflow.com/questions/77928730/loop-validation-copy-output-from-same-range-and-paste-in-different-ranges

There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.