VBA macro only works once.

**tony h** · 10-03-2013, 02:57 PM

hi.
When you get an error message it is most helpful to state which line the error occurs on (it will be highlighted in yellow).

The next thing to do is to follow the code through and see if it is doing what you expect. To do this:
1. click on the first line of the sub and press F8 then carry on pressing F8 and you will see which line is being executed.

My Guess is that find it goes wrong on the workbooks open line. So the question then will be : what value is in myfile. so insert this in the line before: debug.print now, myfile this will display the changing value of myfile in the VBA immediate window.

**randomguy1** · 10-03-2013, 06:42 PM

Thanks for your help.
You are correct about the workbooks open line.
So for the first run it shows all the files within the folder.

10/3/2013 6:27:01 PM        supplier-a.xlsx
10/3/2013 6:27:01 PM        supplier-b.xlsx
10/3/2013 6:27:01 PM        supplier-c.xlsx

Whenever I re-open it it only gives me the first one.

10/3/2013 6:31:32 PM        supplier-a.xlsx

Here is the code after debugging.

'Finding files in folder
Sub LoopThroughDirectory()
    Dim MyFile As String
    Dim erow
    MyFile = Dir("C:\Basic Data Transfer\")
'Making sure file has data
    Do While Len(MyFile) > 0
'Ignoring File in folder
    If MyFile = "zmaster.xlsm" Then
    Exit Sub
    End If
'Open the files in the folder
    Debug.Print Now, MyFile
    ->Workbooks.Open (MyFile)
'To copy specific cells from files
    Range("A2:A3:D2:D3").Copy
'Close the files
    ActiveWorkbook.Close
'Finding first empty row in master spreadsheet
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data onto active sheet, on blank cells just determined
    ActiveSheet.Paste Destination:=Worksheets("sheet1").Range(Cells(erow, 1), Cells(erow, 4))
'Repeating for the next file
    MyFile = Dir
    Loop
    
End Sub

I have the code saved on a non-macro excel spreadsheet and whenever the old one fails I save delete it and make a new spreadsheet with all the code, enable macros, and then run it and it works fine.