[SOLVED] Identical code, different output - reading from two different Excel files

[nErD]

Dear members,

Sorry to disturb you again. I have two code snippets that are identical (except for one variable name):

Snippet 2

Please Login or Register  to view this content.

Snippet 1

Please Login or Register  to view this content.

Snippet 1 works, and copies the right information. Snippet 2 doesn't work, as column F remains empty after the code has been executed.
My suspicion at first that the source file (Filename_From, not shown in the text above) might be corrupted. I recreated the file, but that did not help.

I have attached the two code files in their entirity below. What are your theories?

EDIT: added workbooks.

[TMS]

Why would you have two identical pieces of code barring a variable name?

Why not use one piece of code, the piece that works, and pass the sheet name to it as a variable? One variable that contains the sheet name.

You're doubling the maintenance effort and, as you are finding out, the time to debug code that works in one place but not in another.

Regards, TMS

[nErD]

TMSucks: indeed, you have a good point. The issue is, the two codes pertain to two user forms. The respective user forms are different, and depend on a user choise executed in a seperate module (this code is part of a much larger module I am working with).

My working theory is still that one of the files must be corrupted somehow ... one or several cells barring the way. Right now I am confounded. I will let you know if I figure it out! I will leave this thread open in the mean time.

[royUK]

Why don't you attach the workbook?

[nErD]

Why don't you attach the workbook?

Done; edited the first post.

[snb]

Probably this suffices, assuming 'TO_Sheet_OFA' refers to a worksheet

Please Login or Register  to view this content.

Basically if you use more than 1 workbook you should refer to those workbooks properly, the same applies to worksheets:

Please Login or Register  to view this content.

[TMS]

I'm clearly missing something as there's no forms or code in either of the templates that you have uploaded.


Regards, TMS

[nErD]

Sorry if I have confused you TMShucks!

snb: indeed, I discovered my error and it was due to bad referncing from my part! My lesson of the day: lean how to reference properly.

Here is the solution:

Please Login or Register  to view this content.

Thanks! I hope this can help someone else; I will tag it with the word "referencing".

[snb]

In VBA you'd better not use 'activate' nor 'select'.
You can refer to workbooks, worksheets directly as I showed in my example. As yuo can see you don't even need objectvariables to accomplish this.

[nErD]

You are correct snb; as I was not 100% sure of how you code worked, I used the activate choice to get to work. I will try to modify it according to your code when I understand more about how it works (never done a loop like the one you posted).

[nErD]

snb: I forgot to ask, what is cl in this example?