Opening workbook based on looping value in range

[Ato09]

Hi.

I'm trying to make a macro on Excel 2016 that will open a certain workbook based on data in a determined range. Once the workbook has been open, the data inside the newly opened workbook will be copy and paste into the last blank row of one Main Excel workbook. This will carry on until the macro find empty cell in the determined range.

I have tried using the loop but I can't seem to be able to make it to read from the range.

Sub copyData()
'
' copyData Macro
'
    
    Dim excelName As Variant
    excelName = Range("filesList")
        
    Dim categoryPath As Variant
    categoryPath = Range("category")
    
'   Turn off notification
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
        
        'Code below is to be loop until excelName/filesList returns blank
        Workbooks.Open fileName:="C:\Users\User Name\Desktop\Folder 1\Folder 2\" & categoryPath & "\" & excelName
                
        Windows(excelName).Activate
    
        'enable edit if file is protected
        If Application.ProtectedViewWindows.Count > 0 Then
        Application.ActiveProtectedViewWindow.Edit
        End If
    
        'select data from row 2 to last row
        Rows("2:2").Select
        Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
        Selection.copy
    
        Windows(categoryPath & "_compiled.xlsx").Activate
        Worksheets("Worksheet").Select
        Worksheets("Worksheet").Range("A1").Select
    
        'select last blank cell then paste new data
        If Worksheets("Worksheet").Range("A1").Offset(1, 0) <> "" Then
        Worksheets("Worksheet").Range("A1").End(xlDown).Select
        End If
        ActiveCell.Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        ActiveCell.Offset(1, 0).Select
       
        'close excel file
        Windows(excelName).Activate
        ActiveWindow.Close
        
End Sub