Copy contents from "Child" file to a "Master"

Dear All,

I hope you are all well.

I want to ask your advise, if possible, on a code I have.

Basically, the code copy contents from 4 "Child" files to a "Mater" file.

Everything works smoothly but no data is being copied from the "Child" files.

Is there something wrong on the code:

Please Login or Register  to view this content.

The other thing I've noticed is that if a "Child" file is open, I cannot run the code. Is there a way that even a "Child" file is open, the code can copy it's contents on the background?

Your assistance on this matter will be highly appreciated.

Best regards,
Filipe

It look that you have problem in these rows:

Please Login or Register  to view this content.

and all of that type after this one

you try to put the value of some 501 cells in a single cell

it should be

Please Login or Register  to view this content.

Note that you copy different number of rows 500 or 501.

The second problem - you will get an error if the file is already open, so you should check if file is open before trying to open it

Hm, it's hard without the files, but one more thing - note that you copy data from Monitoring to Master Nomination. Is that right?
Regarding the second problem - my understanding is that you got error when you try to run the code and it try to open already open file. Is that correct?

Please Login or Register  to view this content.

You check for the last row in wsMasterMon and then you copy data to wsMasterNom

I think that's not right.

Regarding the second question - my impression was that the file is already open on the same pc. if it's on a shared drive and some one has already open the file, it should open in read only mode. if it doesn't try to add ReadOnly:=True to the Opne method. Also maybe there is some policy in place that prevents the file from opening if its already open?

Please Login or Register  to view this content.

You check for the last row in wsMasterMon and then you copy data to wsMasterNom

I think that's not right.

Regarding the second question - my impression was that the file is already open on the same pc. if it's on a shared drive and some one has already open the file, it should open in read only mode. if it doesn't try to add ReadOnly:=True to the Opne method. Also maybe there is some policy in place that prevents the file from opening if its already open?

sorry for duplicate post, there was some problem while posting

Let me try to explain.

As the Master file will have the contents of all 4 Child files, I assumed that I need to say in VBA to compile all Child information in the Master one by one or else it might replace values instead of maintaining all Child files information, one after the other.

I did something similar that worked real fine, but that one was only a Master and 1 Child. It copies the contents from one to the other. The difference is that the code copies all table from Child to Master.

Here's the code I used for that file and works perfectly:

Please Login or Register  to view this content.

On this new file I am creating, I need to have all childs info from each sheet to the Master. The problem might be that the "Child" table on each sheet contains about 25 columns and on the Master, I only need about 5 or 9 depending on the sheet and the headers are not in the same order as the "Child" files.

Please let me know if you need further information.

I can try to make a Demo file if you think that will help.

Many thanks in advance for checking this up.

Best regards,
Filipe

I'm looking at your code and I noticed something else

Look at this code

Please Login or Register  to view this content.

iRowNom is the last row in wsMasterNom. after that you copy values to wsMasterNom.Cells(iRowMon, 10). I don't see where in the code up to that point variable iRowMon has been initialized. That code should rise an error, so it's not possible that *Everything works smoothly but no data is being copied from the "Child" files.*

well. I give up

One more thing:

Please Login or Register  to view this content.

It looks like that you overwrite data from J12:J511 with data from L12:L511 both are copied to column 10, starting iRowSug

sorry, not able to help with this