fixing error subscript out of range

Hello Kalil,

I think the error has to do with a missing reset of the array FileList(). I added that and changed the indexing of the array at the same time so that the index number in the array coincides with the row number in the ActiveSheet.

Sub RenameFiles5()
    ' 275

    Const MYPATH As String = "C:\Users\LP PC\Desktop\fl\"

    Dim Ws As Worksheet
    Dim LastCol As Long, LastRow As Long
    Dim R As Long, C As Long            ' rows and columns should be Long integers
    Dim NewName As String, FileExt As String, OldName As String
    Dim FileName As String, FileList() As String, i As Integer

    Set Ws = ActiveSheet        ' dangerous! replace with Worksheets("Tab name")
    LastCol = Ws.Cells(1, Ws.Columns.Count).End(xlToLeft).Column

    For C = 1 To LastCol
        FileExt = Ws.Cells(1, C).Value
        ReDim FileList(2 To 1000)
        i = 1
        FileName = Dir(MYPATH & FileExt & "\")
        While Len(FileName) > 1
            i = i + 1
            FileList(i) = FileName
            FileName = Dir()
        Wend

        If i > 1 Then
            ReDim Preserve FileList(2 To i)

            LastRow = Ws.Cells(Ws.Rows.Count, C).End(xlUp).Row
            For R = 2 To LastRow
                OldName = MYPATH & FileExt & "\" & FileList(R)
                NewName = MYPATH & FileExt & "\" & Ws.Cells(R, C).Value & "." & FileExt
                Name OldName As NewName
            Next R
        End If
    Next C

    MsgBox "Done!"
End Sub

Your original code oscillates between using the ActiveSheet by name and by default. Using it be name should be restricted to when it doesn't have a name. This only happens when a new sheet is created by code. Using it by default isn't good practice because you can never be sure which sheet the user activated unless you call the code with a button. But to use both by name and by default in one procedure intends to confuse the programmer, and that is never a good idea.

Another thing you might like to learn is that when you use Redim Preserve VBA will create a completely new array, transferring all values from the previous to the new, one by one. That is time consuming and should therefore be avoided doing in a loop. It's better to declare the array as too large initially and then redim it only once the required number of elements is known. I have implemented this method in your code. I allowed for 1000 files. You can increase that number without ill effect.