With a VBA can I substitute a Range from another sheet in same workbook from a selected number in a-CodePudding

New here and learning.

I have a Primary Sheet that is used to do the calculations and these depend on the year. I have a VBA that uses the right range (in 'Sheet #2' named "_2022") from that same Workbook.

If I want to use the exact same range but from different Sheets in this Workbook, based on a year that I inscribe in a specific cell in 'Primary Sheet' is it possible?

Here is the base code for my Range (don't worry about the french labeling of variables or Sheets names):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Honoraires As Double
Dim Revenu As Variant
Dim LastValues As Variant
Dim k As Double
Dim drow As Double
Dim DValue As Double
Dim CountRow As Integer


    If Target.Address = "$B$20" Then

        Honoraires = Worksheets("HONORAIRES VS. SALAIRE").Range("B19").Value

        LastValues = 0
        CountRow = 4

    For Each Cell In Worksheets("_2022").Range("B5:B102")

        If Cell.Value > Honoraires Then

What I would like to modify is the --> For Each Cell In Worksheets("_2022").Range("B5:B102") --> The Range will stay the same.

Applicable from a Cell (let's use "E15") in my 'Primary Worksheet' named "HONORAIRE VS. SALAIRE" that will switch and use sheet "_2018" or "_2019" or any other when I type it's name (or from dropdown selection)

Thanks for any help

CodePudding user response：

Something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Honoraires As Double
    Dim Revenu As Variant
    Dim LastValues As Variant
    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$20" Then

        Set wsHVS = ThisWorkbook.Worksheets("HONORAIRES VS. SALAIRE") 'use a worksheet reference...
        Honoraires = wsHVS.Range("B19").Value
        SheetName = wsHVS.Range("E15").Value '<<<
        LastValues = 0
        CountRow = 4
        
        For Each Cell In ThisWorkbook.Worksheets(SheetName).Range("B5:B102").Cells
            If Cell.Value > Honoraires Then