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

Please Login or Register  to view this content.

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]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/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 ...

Please Login or Register  to view this content.

Try this:

Please Login or Register  to view this content.

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

Try shg's code with your:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

and add aline just before the other debugof

Please Login or Register  to view this content.

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.