davesexcel.com

Excel information

Most Popular Excel Questions

Loop Through a Folder of Workbooks

Posted by davesexcel on November 26, 2012 at 12:45 AM

Get data from many workbooks in a folder.



This code will loop through a folder called "WorkBookLoop".

The code then Copies and pastes whatever is in column A:B and paste it to the workbook that is running the code.


 

Sub LoopThroughFolder()

 

    Dim MyFile As String, Str As String, MyDir As String, Wb As Workbook

    Dim Rws As Long, Rng As Range

    Set Wb = ThisWorkbook

    'change the address to suite

    MyDir = "C:\WorkBookLoop\"

    MyFile = Dir(MyDir & "*.xls") 'change file extension

    ChDir MyDir

    Application.ScreenUpdating = 0

    Application.DisplayAlerts = 0

 

    Do While MyFile <> ""

        Workbooks.Open (MyFile)

        With Worksheets("Sheet1")

            Rws = .Cells(Rows.Count, "B").End(xlUp).Row

            Set Rng = Range(.Cells(2, 1), .Cells(Rws, 2))

            Rng.Copy Wb.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

            ActiveWorkbook.Close True

        End With

        Application.DisplayAlerts = 1

        MyFile = Dir()

    Loop

 

End Sub

 

 


Categories: None

Post a Comment

Oops!

Oops, you forgot something.

Oops!

The words you entered did not match the given text. Please try again.

You must be a member to comment on this page. Sign In or Register

0 Comments