as i enter the data in one workbook, it should get updated in relevant other workbook

[vedha]

Hi everybody..
i have n workbooks.
one is main workbook.
second, third.. workbooks are for data to be organised in particular date

first workbook name : All_data
second workbook name : Data_25_4_2015
third workbook name : Data_26_4_2015
fourth workbook...and so on..

my requirement is...
i enter data in All_data. there is a dedicated column to enter the date in this workbook.
based on the date entered, the entire record should be updated in second or relevant work book named with that date.

Example:

record in 'All_data' workbook.
A B C D
Customer1| attended the meeting| need to call again on| 25/4/2015|
Customer2| not attended | need to call on | 27/4/2015|

so now based on the data i entered in all_data workbook, records should get updated in relevant workbooks. if relevant workbook is not available in specified folder it should create one and update.

Purpose:
daily i get number of requests in my business operations. but the request should be processed in a particular date as we suggest.
soon i enter a suggested date, total record of that particular request should be updated in relevant workbook maintained on date basis, so as to enable the caller to easily identify the customers to be called up on that particular date.
hope you can understand my requirement..

thanks for your help.
regards
Vedh

[sktneer]

Hi Vedha,
Welcome to the forum.

Try this to see if this works as per your requirement.
You need to place the following code on Sheet Module of All_Data workbook.
The code will copy a row of Sheet1 of All_Data, once you enter the date in col. D, to the relevant workbook if exists else it will create a workbook and paste the copied data on Sheet1.
The code will create a new workbook in the same folder where the All_Data workbook is placed.

Please Login or Register  to view this content.

For detail see the attached.
To view the code, right click on Sheet1 Tab --> View code.
If you place the above code in another workbook, you will have to add the reference Microsoft Scripting Runtime library.

[vedha]

Thank you very much friend...
your answer solved my problem and its very useful..
but there is a simple logic problem.

actually we get number of records in All_data File.
out of all records there may be multiple records to be collected based
on a specified date into a single another file named with that date.

but here in our code, it is generating new file for a single record. if there is required to enter another record for the same date
it is generating new file with the same name and asking to replace the old one.
it does mean that at last we get only one record in the specified file. but requirement is to collect all the records in that date to the dated file.

it will be more useful if works like this...
Macro should run once we press the save button as we may enter number of records.
then from the all_data work book, number of files will be generated automatically for each date, but only one file for one date.
all the records for a particular date should append in the same workbook named with that date.
as we enter the data everyday in a single main file All_data... program should not append old data again and again.
i mean only new records are to be appended to the relevant workbook if already existed with previous save. if not then only it should create new workbook with that date and append the data.

example:
*****************************************************************
records in 'All_data' workbook on 20/4/2015.
.......A......|.............. B ............|........... C ..............|...... D ....|
Customer1| attended the meeting.| need to call again on| 25/4/2015|
Customer2| not attended............ | need to call on....... | 27/4/2015|
Customer3| will attend ...............| need to call on........| 27/4/2015|
Customer4| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer5| will decide later.........| need to call on........| 26/4/2015|
customer6| confirmed.................| need to call again on| 25/4/2015|

now i should get different files, once i save this all_data file as follows..

file name:
date_25_4_2015
content :
Customer1| attended the meeting.| need to call again on| 25/4/2015|
customer6| confirmed.................| need to call again on| 25/4/2015|

file name:
date_26_4_2015
content :
Customer4| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer5| will decide later.........| need to call on........| 26/4/2015|

file name:
date_27_4_2015
content :
Customer2| not attended............ | need to call on....... | 27/4/2015|
Customer3| will attend ...............| need to call on........| 27/4/2015|

********************************************************************
records in 'All_data' workbook on 21/4/2015.
.......A......|.............. B ............|........... C ..............|...... D ....|
***old records***
Customer1| attended the meeting.| need to call again on| 25/4/2015|
Customer2| not attended............ | need to call on....... | 27/4/2015|
Customer3| will attend ...............| need to call on........| 27/4/2015|
Customer4| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer5| will decide later.........| need to call on........| 26/4/2015|
customer6| confirmed.................| need to call again on| 25/4/2015|

***new records***
Customer7| attended the meeting.| need to call again on| 25/4/2015|
Customer8| not attended............ | need to call on....... | 27/4/2015|
Customer9| will attend ...............| need to call on........| 27/4/2015|
Customer10| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer11| will decide later.........| need to call on........| 26/4/2015|
customer12| confirmed.................| need to call again on| 28/4/2015|

now i should get my files updated as follows...

file name:
date_25_4_2015
content :
Customer1| attended the meeting.| need to call again on| 25/4/2015|
customer6| confirmed.................| need to call again on| 25/4/2015|
Customer7| attended the meeting.| need to call again on| 25/4/2015|

file name:
date_26_4_2015
content :
Customer4| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer5| will decide later.........| need to call on........| 26/4/2015|
Customer10| asked to call again.....| need to remind.......| 26/4/2015|
Cusotmer11| will decide later.........| need to call on........| 26/4/2015|

file name:
date_27_4_2015
content :
Customer2| not attended............ | need to call on....... | 27/4/2015|
Customer3| will attend ...............| need to call on........| 27/4/2015|
Customer9| will attend ...............| need to call on........| 27/4/2015|

file name:
date_28_4_2015
content :
customer12| confirmed.................| need to call again on| 28/4/2015|

hope m explained better to make u solve my problem.

please fix my issue..

thanks
regards
Vedha

[sktneer]

hmm.
Please find the attached and see if this works now as expected.

Please Login or Register  to view this content.

[vedha]

excellent my dear friend..
thanks a lot..
your help is highly appreciable.

actually m not worked much with programming. still i can understand if u suggest me to change the code wherever applicable.
m an SAP consultant.

anyway can i know where are the changes to be made, if this source code to be used for my worksheet containing huge data.
means...
date field row may not be 4th row.
required number of columns to be copied may not be 4.

thanks
regards
Vedha

[sktneer]

Hi Vedha,

Here is the code with some added comments, so you can tweak the code as per your requirement

Please Login or Register  to view this content.

[vedha]

Hi good morning...
m unable to make changes according to my requirement.
pl see my file once and suggest me..

thank you..

vedha.

[sktneer]

See the attached.

[vedha]

thank you very much...
its working up to my requirements. thanks a lot..

regards
Vedha

[sktneer]

You're welcome.

If that takes care of your original question, please mark your thread as Solved by selecting Thread Tools (just above your first post) --> Mark thread as solved.