Macro to Open File in Reference Cell

[fchiav]

Hi,

I'm not skilled in VBA, so the following is created from Macro Recorder.

The macro below functions, though actions 422.csv which was the original file when recording the macro.
Rather, i'm wanting the macro to action the respective csv file for that same worksheet.

My data is saved in csv files (in same directory as workbook) with names nnn.csv. Each subsequent file is the next number in sequence.

Each worksheet references it respective csv data file in the cell AI17

AH14 contains a hyperlink to this respective csv file. It uses value in cell AI17 for link location. This hyperlink functions correctly by itself.

Thanking you in advance for your time & assistance on my query.

Sub Copy_Data()
'
' Copy_Data Macro
'


'
    Calculate
    Range("AH14").Select
    Workbooks.Open Filename:= _
        "C:\Tests\422.csv"
    ActiveWindow.Visible = False
    Windows("422.csv").Visible = True
    ActiveCell.SpecialCells(xlLastCell).Select
    Range(Selection, Cells(1)).Select
    Selection.Copy
    Windows("Test System.xlsm").Activate
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("TS (422)").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("TS (422)").AutoFilter.Sort.SortFields.Add Key:= _
        Range("U3"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortTextAsNumbers
    With ActiveWorkbook.Worksheets("TS (422)").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Calculate
    Range("AH41:AY41").Select
    Selection.Copy
End Sub