EXCEL VBA: how to increase the speed of this code

Question

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?

This code works makes it inappropriate for SO. It would, however, be a great fit for Code Review. I'd recommend posting it there for some great help. — FreeMan
– FreeMan, Commented Jun 24, 2015 at 13:49
I'm voting to close this question as off-topic because it is functional code that he is looking to optimize. It belongs on Code Review. — FreeMan
– FreeMan, Commented Jun 24, 2015 at 13:49
Okei.. I don't know about Code review.. I will post it there and delete it from here. Thanks — Abdul Shiyas
– Abdul Shiyas, Commented Jun 24, 2015 at 13:52

Paul Ogilvie · Accepted Answer · 2015-06-24 21:41:21Z

1

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.

answered Jun 24, 2015 at 21:41

Paul Ogilvie

25.4k4 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

EXCEL VBA: how to increase the speed of this code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related