Paste below a particular row using Offset

Question

I am trying to do analysis of different scenarios.

Let's say row 1 has the current/basecase result.
I want to paste results from row 1 to the rows below corresponding to the scenario.
Example - Scenario 1 on row 2, Scenario 2 on row 3, etc.

The macro conducts this analysis one scenario at a time.
When I specify to run scenarios on rows 2-10, it will paste correctly on rows 2-10.

However when I run scenarios on row 5-10, it will paste on rows 2-7 not rows 5-10.

This code works for other workbooks.

Sub Add_sce()

Application.ScreenUpdating = False
Application.Calculation = xlManual

'Clearing cell contents
Sheets("Sensitivity Analysis").Range("SCE_clean").Select
Selection.ClearContents

'Creating an area to paste results of each simulation for each scenario
Worksheets("Appendix-PSA").Select
Range("PSA_iteration") = Range("addscen_iteration")

Range("PSA_sim_pasted_results").Select
Selection.ClearContents
Range("PSA_sim_current_results").Select
Selection.Copy
Range("PSA_sim_pasted_results").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False

Count = 0 / (Range("AD_stop").Value - Range("AD_start").Value)
Application.StatusBar = Count

'Replacing basecase values for variable with scenario values
Dim i As Integer

For i = Range("AD_start").Value To Range("AD_stop").Value

    Sheets("Model Parameters").Range(Sheets("Sensitivity Analysis").Range("V" & i & ":V" & i)) = _
      Sheets("Sensitivity Analysis").Range("Z" & i & ":Z" & i)

    If Sheets("Sensitivity Analysis").Range("W" & i & ":W" & i) <> "" Then
        Sheets("Model Parameters").Range(Sheets("Sensitivity Analysis").Range("W" & i & ":W" & i)) = _
          Sheets("Sensitivity Analysis").Range("AA" & i & ":AA" & i)
    Else
    End If

    If Sheets("Sensitivity Analysis").Range("X" & i & ":X" & i) <> "" Then
        Sheets("Model Parameters").Range(Sheets("Sensitivity Analysis").Range("X" & i & ":X" & i)) = _
          Sheets("Sensitivity Analysis").Range("AB" & i & ":AB" & i)
    Else
    End If

    If Sheets("Sensitivity Analysis").Range("Y" & i & ":Y" & i) <> "" Then
        Sheets("Model Parameters").Range(Sheets("Sensitivity Analysis").Range("Y" & i & ":Y" & i)) = _
          Sheets("Sensitivity Analysis").Range("AC" & i & ":AC" & i)
    Else
    End If

    'Starting simulation
   
    Worksheets("Appendix-PSA").Select
    Call PSA

    Calculate
    
    '**Problem is somewhere here - it is supposed to copy from range add_result and 
    ' Paste it in the row specified by 
    ' the scenario number i and starting row number AD_start

    Worksheets("Sensitivity Analysis").Select

    Range("add_result").Select
    Selection.Copy
    ActiveCell.Offset(i + 1 - Range("AD_start"), 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False

    'Replacing scenario values with default values
    Worksheets("Model Parameters").Select
    Call Default

    Worksheets("Sensitivity Analysis").Select

    Count = Round((i - Range("AD_start").Value) / (Range("AD_stop").Value - Range("AD_start").Value), 2) * 100 & " %"
    Application.StatusBar = Count

Next i

Worksheets("Appendix-PSA").Select
Range("PSA_sim_pasted_results").Select
Selection.Copy
Range("PSA_sim_current_results").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False

Worksheets("Sensitivity Analysis").Select
Range("a1").Select
Application.Calculation = xlAutomatic
Calculate

Answer 1

This is what your code should look like if you want to find errors in it.

Option Explicit

Sub Add_sce()
    ' 172

    Dim WsSA        As Worksheet                ' "Sensitivity Analysis"
    Dim WsPSA       As Worksheet                ' "Appendix-PSA"
    Dim WsMod       As Worksheet                ' "Model Parameters"
    Dim RngName     As String
    Dim ADstart     As Long
    Dim ADstop      As Long
    Dim i           As Long

    ' declare referenced worksheets 
    Set WsSA = Worksheets("Sensitivity Analysis")
    Set WsPSA = Worksheets("Appendix-PSA")
    Set WsMod = Worksheets("Model Parameters")

    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
    End With
    
    ' Clearing cell contents
    WsSA.Range("SCE_clean").ClearContents

    With WsPSA
        ' Creating an area to paste results of each simulation for each scenario
        '   This code doesn't create anything.
        '   It assigns the value from one range to the other.
        '   The two ranges must be of the same size and on the same sheet.
        .Range("PSA_iteration").Value = .Range("addscen_iteration").Value
        
        ' clearing "PSA_sim_pasted_results" should be superfluous
        ' because any existing contents would be over-written
        .Range("PSA_sim_pasted_results").ClearContents
        .Range("PSA_sim_current_results").Copy
        .Range("PSA_sim_pasted_results").PasteSpecial Paste:=xlPasteValues, _
                                                      Operation:=xlNone, _
                                                      SkipBlanks:=False, _
                                                      Transpose:=False
        
        ' reading from the sheet takes a lot of time
        ' therefore don't read the same value more than once
        ADstart = .Range("AD_start").Value
        ADstop = .Range("AD_stop").Value
        
        ' This formula will always either be 0 or #DIV/0 Error
        Application.StatusBar = 0 / (ADstop - ADstart)
    End With

    ' Replacing basecase values for variable with scenario values
    For i = ADstart To ADstop
        RngName = WsSA.Cells(i, "V").Value
        WsMod.Range(RngName).Value = WsSA.Cells(i, "Z").Value
        
        If WsSA.Cells(i, "W").Value = "" Then
            RngName = WsSA.Cells(i, "W").Value
            WsMod.Range(RngName).Value = WsSA.Cells(i, "AA").Value
        End If
        
        If WsSA.Cells(i, "X").Value = "" Then
            RngName = WsSA.Cells(i, "X").Value
            WsMod.Range(RngName).Value = WsSA.Cells(i, "AB").Value
        End If
        
        If WsSA.Cells(i, "Y").Value = "" Then
            RngName = WsSA.Cells(i, "Y").Value
            WsMod.Range(RngName).Value = WsSA.Cells(i, "AC").Value
        End If

        ' Starting simulation
        Call PSA(WsPSA)
        Calculate
        
        ' Problem is somewhere here:-
        ' it is supposed to copy from range "add_result" and Paste it in the row
        ' specified by the scenario number i and starting row number AD_start
        ' The problem hails from the ActiveCell:-
        '   ActiveCell.Offset(i + 1 - Range("AD_start"), 0)
        ' That's a cell clicked by the user. It needs to be replaced with
        ' a cell determined by the macro.

        WsSA.Range("add_result").Copy
        ' can't figure out even the sheet you want to paste to, let alone the column.
        ' Specify the TopLeft cell as Cells([Row], [Column])
        WsSA.Cells(i + 1 - ADstart, "AA").PasteSpecial Paste:=xlPasteValues, _
                                                       Operation:=xlNone, _
                                                       SkipBlanks:=False, _
                                                       Transpose:=False

        ' Replacing scenario values with default values
        Call Default(WsMod)
        Application.StatusBar = Format((i - ADstart) / (ADstop - ADstart), "0.00%")
    Next i

    With WsPSA
        .Range("PSA_sim_pasted_results").Copy
        .Range("PSA_sim_current_results").PasteSpecial Paste:=xlPasteValues, _
                                                       Operation:=xlNone, _
                                                       SkipBlanks:=False, _
                                                       Transpose:=False
    End With

    With Application
        .ScreenUpdating = True
        .Calculation = xlAutomatic              ' this triggers calculation
        .CutCopyMode = False
        .StatusBar = "Done"
    End With
    
    With WsSA
        .Activate
        .Cells(1, 1).Select
    End With
End Sub

Private Sub PSA(WsPSA As Worksheet)

    With WsPSA
        ' precede your range and cell addresses with a period
        ' to make them refer to WsPSA
    End With
End Sub

Private Sub Default(WsMod As Worksheet)

    With WsMod
        ' precede your range and cell addresses with a period
        ' to make them refer to WsMod
    End With
End Sub

But before we can look for errors the transformation job must be completed. Your two subroutines, Default and PSA presumably work on the ActiveSheet which your code changes all the time. My code doesn't do that. Therefore the subs must be told which sheet to work on. The above code demonstrates how the worksheet object is passed to the sub and how the sub receives the argument. Now you should change the code of the two procedures to work with the given worksheets. Make sure to remove all Select or Activate statements in the manner I have demonstrated in the above code.

And now, the error:- It is caused by the reference to the ActiveCell. That is the cell last clicked by the user before starting the macro. Your code tries to determine the target cell relative to it. Specify the correct target cell in this part of the above code before you attempt to run it.

WsSA.Range("add_result").Copy
' can't figure out even the sheet you want to paste to, let alone the column.
' Specify the TopLeft cell as Cells([Row], [Column])
WsSA.Cells(i + 1 - ADstart, "AA").PasteSpecial Paste:=xlPasteValues, _

Needless to say, I couldn't run the code. For errors of all kinds that you may find I apologize. The point is that you can find them and can correct them but if you need help, please ask.