Dynamic Lookup List to copy data onto master sheet

[knightofni]

Hello all,

I currently have a Macro that opens a workbook, copies data from a sheet and then closes it again, this works fine. However, I now have 43 workbooks that I need to work with and really don't want to have to write out that many file names in the macro.

Therefore I was wondering if anyone could help devise a dynamic loop function that would:

• Use a list of filenames held in another spreadsheet to open the file
• Copy Data onto master sheet
• Close File
• Repeat through list of filenames

NB: The number of files needed may increase over time which is why I would like the function to be dynamic.

Many thanks

 

[pboltonchina]

Why use a list? Put all the files in one folder and try this

Sub LoopThroughFolder()
    Dim folderPath As String
    Dim filename As String
    Dim WB As Workbook
    
    folderPath = "C:\temp\excel\" 'change to suit your folder path
    
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    
    filename = Dir(folderPath & "*.xls")
    Do While filename <> ""
        Set WB = Workbooks.Open(folderPath & filename)
        
        'Call a subroutine here to operate on the just-opened workbook
        Call YourMacro(WB)
        
        WB.Close False
        filename = Dir
    Loop
    
End Sub