Loop to Open All Files in a Directory and Copy range to a Master File

pliskers

Active Member
Joined
Sep 26, 2002
Messages
461
Office Version
  1. 2016
Platform
  1. Windows
I need some code that will reside in a Master file where data ranges in each file within a directory needs to be copied to.

Simply stated, the loop code will open every file in a single directory and, one at a time, will copy/paste to the Master.

I can handle the code to do the copying/pasting. What I need is the loop code to go through all of the Excel files in the directory and stop when the process has been performed on the last file.

Been searching the web, but am getting errors when I try to run the code that I've found so far.

I'm running Excel 2007, FYI. Thanks in advance!
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Here's one way...

Code:
Option Explicit

Sub test()

    Dim wkbDest As Workbook
    Dim wksDest As Worksheet
    Dim wkbSource As Workbook
    Dim wksSource As Worksheet
    Dim MyPath As String
    Dim MyFile As String
    
    Application.ScreenUpdating = False

    Set wkbDest = ThisWorkbook
    Set wksDest = wkbDest.Worksheets("Sheet1")[COLOR="SeaGreen"] 'change the destination sheet name accordingly[/COLOR]

    MyPath = "C:\Users\Domenic\Desktop\"
    
    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"
    
    MyFile = Dir(MyPath & "*.xls")
    
    Do While Len(MyFile) > 0
        Set wkbSource = Workbooks.Open(MyPath & MyFile)
        Set wksSource = wkbSource.Worksheets("Sheet1")[COLOR="SeaGreen"] 'change the source sheet name accordingly[/COLOR]
       [COLOR="SeaGreen"] 'Your copy/paste code here[/COLOR]
        wkbSource.Close savechanges:=False
        MyFile = Dir
    Loop
    
    Application.ScreenUpdating = True
    
    MsgBox "Completed...", vbInformation
    
End Sub
 
Upvote 0
Hi domenic,

i tried your code and it worked., thanks.
however, i stumbled in this line: (See Below)

Set wksSource = wkbSource.Worksheets("Sheet1") 'change the source sheet name accordingly

this line can only get the data with the name of "Sheet1" in the source sheet.
can you check if we can get data even if the name of sheet is different? (e.g. sheet2, test3, text4, etc.)

regards, melson
</pre>
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top