Macro to open files based on string variable

Hi all!

Another saga in my macro development. If someone could please advise how to do (if possible!) the requested would be much appreciated.

Basically I have a workbook with some titles in a row, say AAA BBB CCC DDD etc.

Each of these titles represents a workbook to open in the same directory (i.e. AAA is the title of one workbook, BBB the title of the next etc.)

I am drafting a macro but a bit stuck on how to tell Excel to use the string variable stored in a single dimension array (the workbook row with the titles contained within) as the last part of the filename directory e.g.:

C:\My documents\AAA
C:\My documents\BBB
C:\My documents\CCC


My macro as it stands:

Sub DOWNLOAD()

'Macro to download from multiple workbooks.

Dim pfx(100) As String 'Single dimension array defining variable as string.
Dim Target As String 'Defining target information as string.



Application.ScreenUpdating = False 'Turns off screen updating.

Worksheets("INPUT").Select 'Selects the worksheet to find the depot prefix.



For j = 1 To 10 'Defines how long to search.

Cells(2, j).Select 'Defines cell reference to search (row, column).

pfx(j) = ActiveCell 'Reading in values of strings as pfx.

Next j 'Reads for all values of j into single dimension array.



Let Filename = pfx(1)

Workbooks.Open Filename:="C:\My documents\pfx(1)"

Range("B4:B100").Copy 'Range of cells to copy from management accounts.

Windows("MACRO TEST").Activate 'Navigates back to the macro test workbook.

Worksheets("INPUT").Select 'Navigates back to the macro test worksheet.

Cells(4, 2).Select 'Selects the appropriate cell to paste into.

Selection.PasteSpecial 'Pastes the data as values.
    Paste = xlPasteValues
    operation = xlNone
    skipblanks = False
    Transpose = False

Application.ScreenUpdating = True 'Turns on screen updating.


End Sub

If anyone can help would be much appreciated!

All the best,

Coeus.