Sharing/copying data from 1 file to multiple files (automatically)

I am a newbie in excel and would like some help to automatically share the data (in cells A1:A100 - Z1:Z100) entered/saved in the main file to populate multiple files such as File1, File2, etc. The data in columns A and Y of the main file need to be "joined" to form the required data in column K in File4.

I know most people are looking for the reverse where they want to consolidate data from multiple files into one big file, but I need the opposite.

Thanks for your help.

A sample of the data and the expected results could help immensely...

Please see the attached files. The Main file contains all the data which is then distributed to various files (File1, File2, File3 and File4) depending on the column headings. File4's column K contains data joined from columns A and Y of the Main file.

Thanks!!

this is very simplistic but works, it assumes that all files are opened and its ready to be
triggered by a selectchange auto sub

Please Login or Register  to view this content.

Ok figured out how and where to enter the "code" in excel using the developer tab and running it... I got an error 9... Subscript out of range.....not sure what to do about that??
Please help in getting rid of the error, thanks rcm for your help!!

VBA is very vague with errors but do all mentioned files open ?

If this goes on permanently then it can be modified to update just the record that has changed

I had all the files open according to your instructions.....so when I ran the code, all the files closed except for the main file and the main file's data was changed (corrupted the cells... names were replaced with numbers) which is something I was not expecting

Just to clarify, I am trying to take the data from the main file (one row shown in the main excel file uploaded before) to the various files depending on the column headings as shown in File1, File2, File3 and File4. I hope you can help this problem....thanks rcm!

still need help with this... thanks.

OK. Let me ask questions..
like I said the code is very simplistic so I may have to twick it a bit

Are the fields in file1 thru file4 the actual fields that you want to transfer using the real data?
Is the order of the fields exactly as posted in the example?
Is Main the only source of information?
Is this a repetitive action or is it once and done?

I´m asking all of these questions because the sample file are filled with the macro posted...but your real data might have another structure

Sorry for the late response....see below for my answers.. Thanks again for your help!

Originally Posted by rcm

OK. Let me ask questions..
like I said the code is very simplistic so I may have to twick it a bit

Are the fields in file1 thru file4 the actual fields that you want to transfer using the real data? --- Yes
Is the order of the fields exactly as posted in the example? -- Yes (but what if I needed to change the order of the fields, would I be able to manage that too with a couple of minor changes in the code?)
Is Main the only source of information? --- Yes
Is this a repetitive action or is it once and done? --- Repetitive would be ideal; however if I can do it for say 100 entries at a time, clear the Main file and then load it again with more entries, if that is easier to do?

I´m asking all of these questions because the sample file are filled with the macro posted...but your real data might have another structure

The whole process can be automated, as soon as you fill a line it can be automatically transferred to the 4 files and marked as processed . Would that be what you want?

Clicking a button would be a better option so it can be clicked when ready to transfer the data...if that's more difficult, then the automatic transfer suggestion you mention will work too. Thanks!

Originally Posted by rcm

The whole process can be automated, as soon as you fill a line it can be automatically transferred to the 4 files and marked as processed . Would that be what you want?