VBA macro only works once.

Hey guys,

So I have code written to merge the data from multiple spreadsheets into one. It works fine when I first write the program and press play, I can even press play multiple times and it will append the data to the bottom of the spreadsheet. However, if I save it and close it (with or without hitting play) and try to run the program once I open it I get an error.
VBA Error.jpg
Is there something I did wrong or am missing? Any help would be most appreciated.

Here is a copy of the code that I used.
'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
    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

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.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(I have added the code tags for you since you are new to the forum. Please adhere to our rules in the future.)

Maybe ...

Sub LoopThroughDirectory()
    Dim sFile       As String

    sFile = Dir("C:\Basic Data Transfer\*.*")

    Do While Len(sFile)
        If sFile <> "zmaster.xlsm" Then
            Debug.Print Now, sFile
            Workbooks.Open sFile
            Range("A2:D3").Copy Sheet1.Cells(Rows.Count, "A").End(xlUp)(2)
            ActiveWorkbook.Close SaveChanges:=False
            sFile = Dir
        End If
    Loop
End Sub

Try this:

Sub LoopThroughDirectory()
    Dim sFile       As String

    sFile = Dir("C:\Basic Data Transfer\*.xl*")

    Do While Len(sFile)
        Debug.Print Now, sFile
        If LCase(sFile) <> LCase(ThisWorkbook.Name) Then
            Workbooks.Open sFile
            Range("A2:D3").Copy Sheet1.Cells(Rows.Count, "A").End(xlUp)(2)
            ActiveWorkbook.Close SaveChanges:=False
            sFile = Dir
        End If
    Loop
End Sub

Try stepping through and find out what line causes the crash.

Try shg's code with your:

Dir("C:\Basic Data Transfer\")

Well I am glad you are progressing. Isuggest changing the workbooks open line to

            Workbooks.Open "C:\Basic Data Transfer\"  &  sFile

and add aline just before the other debugof

debug.print curdir$

I suspect that might show the problem.

You're welcome!

That's great. For the sake of completeness it would be nice to see the final code.

Also I presume you have realised that you don't need to leave the debug lines in the code. They only display values to help you understand what is going on.