Copy a specific Range from a 'named' sheet to specific Range in Primary sheet depending on-CodePudding

I have a VBA code that uses a Range from a specific sheet (depending on a cell value that = year in my primary sheet) and now it works great it's all because of you guys that it went so well so thank you all for that.

Now I would like to take a Range from (lets say sheet named "2017") The Range is specific in every sheet for every year of data --> Always = .Range("O14:S25")

So, in my Primary Worksheet "HONORAIRES VS. SALAIRE", I would like to paste the Range form Sheet "2017" and replace the .Range("C4:G15").Value in my Primary Worksheet depending on the year in my Cell = E18.

*The Cells.Range contains String and / or Numeral Values

Here is the base of my VBA:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Honoraires As Double
Dim Revenu As Variant
Dim LastValues As Variant
Dim Cotisations As Range
Dim k As Double
Dim drow As Double
Dim DValue As Double
Dim CountRow As Integer, SheetName As String, wsHVS As Worksheet



    If Target.Address = "$B$23" Then
        Set wsHVS = ThisWorkbook.Worksheets("HONORAIRES VS. SALAIRE")
        Honoraires = wsHVS.Range("B22").Value
        SheetName = wsHVS.Range("E18").Value
        
        LastValues = 0
        CountRow = 4
    
    For Each Cell In ThisWorkbook.Worksheets(SheetName).Range("B5:B102").Cells
        If Cell.Value > Honoraires Then

            Worksheets("HONORAIRES VS. SALAIRE").Range("I22").Value = (Honoraires * ThisWorkbook.Worksheets(SheetName).Range("D"   CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("K22").Value = (Honoraires * ThisWorkbook.Worksheets(SheetName).Range("F"   CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("J22").Value = ((Honoraires - ThisWorkbook.Worksheets(SheetName).Range("B"   CStr(CountRow)).Value) * ThisWorkbook.Worksheets(SheetName).Range("I"   CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value
            Worksheets("HONORAIRES VS. SALAIRE").Range("L22").Value = ((Honoraires - ThisWorkbook.Worksheets(SheetName).Range("B"   CStr(CountRow)).Value) * ThisWorkbook.Worksheets(SheetName).Range("J"   CStr(CountRow)).Value) / Worksheets("HONORAIRES VS. SALAIRE").Range("$C$22").Value

            Exit For

        ElseIf LastValues < Honoraires Then

            CountRow = CountRow   1
            LastValues = Cell.Value

        End If

    Next Cell

End If

Let me know....

PS:

Last thing to mention. this doesn't need a macro to work, you can simply use the "Indirect" native excel formula. Example: on your primary sheet write the following in cell "C4":

=INDIRECT("'"&$E$18&"'!O14")

It will now update to whatever is in cell "O14" every time the "Year" cell is updated.