Accessing Files in a Directory with Varying Titles

[Grace1918]

Hi there,

I am wanting to copy information from other sheets and paste it on my current sheet. The other sheets are pay rate sheets and a new one comes out every year. The beginning of the file names are all the same but the year changes for each year(ex. Rate Sheet 2020, Rate Sheet 2021).

I was just wondering if there was a way for a macro to go through and open all the rate sheets that are the current year and future years but not open past years. I saw other Forums about doing this with missing title parts but i want to make sure it only starts at the current year. I thought maybe (=YEAR(TODAY())) could be used.

This is what I have so far

Dim year As Date
Dim filefound As Boolean

    Windows("Rate Sheet 2020.xlsx").Activate
     Filename = "P:\Project Forecasts\Forecast Reference Files\Rate Sheet " & =YEAR(TODAY()) & ".xlsx"
     Workbooks.Open Filename:= _
        "P:\Project Forecasts\Forecast Reference Files\Rate Sheet 2020.xlsx"
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("P0## 201# - PROJECT NAME R .0.5 Grace.xlsm").Activate
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
      
    Windows("Rate Sheet 2021.xlsx").Activate
    Workbooks.Open Filename:= _
        "P:\Project Forecasts\Forecast Reference Files\Rate Sheet 2021.xlsx"
    Range("B2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("P0## 201# - PROJECT NAME R .0.5 Grace.xlsm").Activate
    Range("B20").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

or

'get current  year
    
   year = Format(Now(), "YYYY")
    
'open rate sheet
    Filename = "P:\Project Forecasts\Forecast Reference Files\Rate Sheet " & year & ".xlsx"
    
    On Error GoTo FileReadError1
    noerror = False
    
    If Not Dir(Filename, vbDirectory) = vbNullString Then
        Set wkb = Workbooks.Open(Filename)
        noerror = True
    End If
    
'the only way to go in here is if the Rate Sheet file is not the right name, or is not in the right path
    If noerror = False Then
FileReadError1:
        MsgBox "The file: " & Filename & " does not exist!  Please select an alternative location for this file."
    'Select new location for the Rate Sheet
        filefound = False
        Do Until filefound = True
            Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
            With fDialog
                .InitialFileName = "C:\"
                .AllowMultiSelect = False
                .Title = "Select the Rate Sheet " & year & " file"
                If .Show = True Then
                    Filename = Dir(.SelectedItems(1))
                Else
                    MsgBox "No rate sheet selected!  Exiting routine."
                    GoTo JUMPTOEND
                End If
            End With
            
            If Filename = "Rate Sheet " & year & ".xlsx" Then
                filefound = True
                Set wkb = Workbooks.Open(Filename, ReadOnly:=True)
            Else
                MsgBox "You selected the file: " & """" & Filename & """" & ".  Please select the file: " & """" _
                & "Rate Sheet " & year & ".xlsx" & """"
            End If
        Loop
    End If
    
'get data from the rate sheet and close it
    Set SHT = wkb.Worksheets(1)
    maxrows = SHT.Range("B1048576").End(xlUp).Rows(1).Row
    maxcol = SHT.Range("ZZ2").End(xlToLeft).Columns(1).Column
    RateData = SHT.Range(SHT.Cells(1, "A"), SHT.Cells(maxrows, maxcol))
    wkb.Close (False)

But I want the macro to change to only contain relevant years.

Thanks in advance for anyone who can help