Copy a specific Range from a 'named' sheet to specific Range in Primary sheet depending on Cell Value in Primary sheet

Question

I have VBA code that uses a Range from a specific sheet (depending on a cell value that = year in my primary sheet).

---

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

In my Primary Worksheet "HONORAIRES VS. SALAIRE", I would like to paste the Range from 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

The base of my VBA code:

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

"Primary Sheet"

"2017" Sheet

I feel completely lost after trying a With and a For code line.

Answer 1

I think this is all you need to complete your task. Let me know if I'm missing the point in any way.

Sub LoadYear()

    Dim ReportYear As String
    ReportYear = Worksheets("Primary Sheet").Range("E18").Value
    
    If Not WorksheetExists(ReportYear) Then
        MsgBox "Selected Sheet does not appear to exist. " & _
            "Please find the sheet and ensure the sheet name is in ""yyyy"" format.", _
            vbOKOnly, "ERROR, Santa Clause not behaving."
        Exit Sub
    End If
    
    Worksheets("Primary Sheet").Range("C4:G15").Value = _
        Worksheets(ReportYear).Range("O14:S25").Value

End Sub
Function WorksheetExists(shtName As String, Optional wb As Workbook) As Boolean

    '>>> Borrowed from @Tim Williams on StackOverflow, thanks Tim.
    
    Dim sht As Worksheet
    If wb Is Nothing Then Set wb = ThisWorkbook
    On Error Resume Next
    Set sht = wb.Sheets(shtName)
    On Error GoTo 0
    WorksheetExists = Not sht Is Nothing
End Function

And I would facilitate it with a load button rather than a worksheet event:

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.