Dynamic Lookup List to copy data onto master sheet

knightofni

New Member
Joined
Sep 22, 2010
Messages
12
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
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,095
Why use a list? Put all the files in one folder and try this
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,514
Messages
5,572,602
Members
412,474
Latest member
DZChaser
Top