VBA can't open external workbook and copy data at the same time

I cannot say for sure without your file, but it looks like you have this code in a standard module (e.g., Module1). This means that all unqualified references default to ActiveWorkbook. When you open a new file, that file becomes ActiveWorkbook. However, it appears that you intend these to reference ThisWorkbook. This is why I discourage unqualified references in a standard module under most circumstances.

I would think if my diagnosis is correct, you would be getting error messages, but you don't mention that. But if you qualify the references this may start working for you.

Also, sometimes an error is not where you think it is, and it is always best to provide all of the code, not just the lines where you think there is a problem. And even better, provide the file for testing.

Change sheet name to match actual sheet name.

Please Login or Register  to view this content.