CODEWITHSUNDEEP
vbaexcel
Abdul Shiyas
Abdul Shiyas

Reputation: 401

EXCEL VBA: how to increase the speed of this code

This code works. Since it has too many loop, loop inside loop, this works very slowly. How can we increase the speed of this program? I am trying to copy a range from one file to another file of same name in different folder. I have folders named "A","B","C"..."G" inside "c:\Charts\1\" & "c:\Charts\0\". Inside each folders "A","B","C"..."G" there are files named 1,2,3,4,..10.

My code is like this

vArr = Array("A", "B", "C", "D", "E", "F", "G")
Dim fileName1, Pathname1 As String
Pathname1 = "c:\Charts\1\"
Pathname="c:\charts\0\"
For Each vFile1 In vArr1
    fileName1 = Dir(Pathname1 & vFile1 & "\" & "*.xlsx")
    Do While fileName1 <> ""
        For Each vFile In vArr
            filename = Dir(Pathname & vFile & "\" & "*.xlsx")
            Do While filename <> ""
                If filename = fileName1 Then
                Set WB1 = Workbooks.Open(Pathname1 & vFile & "\" & fileName1)
                    WB1.Application.ScreenUpdating = False
                    WB1.ActiveSheet.Range("M1:M30").Copy
                    WB1.Close (False)
                Set WBD1 = Workbooks.Open(Pathname & vFile & "\" & filename)
                WBD1.ActiveSheet.Range("C1").Select
                    WBD1.ActiveSheet.Paste
                    WBD1.ActiveSheet.Cells(1, 3).Value = "HSI Q4 2014-15"
                    WBD1.Close (True)
                    filename = Dir()
                Else

                End If
                fileName1 = filename
            Loop
        Next
    Loop
Next

How can we make this work faster?

Upvotes: 0

Views: 85

Answers (1)

Paul Ogilvie
Paul Ogilvie

Reputation: 25286

I am amazed that this code "does what it needs to do". I see the following bugs.

Bug1: There can be only one Dir() open at the same time. You use two nested Dir()s.

Bug2: look at the loop:

Do While filename <> ""
    If filename = fileName1
    ...
    End if
    fileName1 = filename
Loop

If at the first iteration filename isn't equal to filename1 then it will be at the second iteration, however, it is not assured that the file exists in pathname1 as it has not been obtained by a call to the topmost Dir() (which you can't because the second Dir() replaced the first one).

Maybe you should first specify what this code is supposed to do.

Upvotes: 1

Related Questions