1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Update selected data Automatically

Discussion in 'Business Applications' started by manukit, Jan 1, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. manukit

    manukit Thread Starter

    Joined:
    Jan 1, 2013
    Messages:
    12
    Hello All,

    I have a doubt on excel, please share your ideas for the below mentioned query:
    First of all, I have created New Worksheet & saved as "MAIN" in different location. I have the sub-ordinates of 15 members, who work on their excel sheet with different location.

    Here, i would like to copy some selected contents or copy several columns from their worksheet to the "MAIN" worksheet. I used to do it manually. Now, my question is that is there any other way to sort this automatically?

    I had tried by applying some formulas, but it worked only for a single person. But i required for all 15 members data to be copied at once.

    Thanks,
    Kit
     
  2. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Kit,

    Welcome to the forum.
    I'm not sure about using formulas, but you can use code for this purpose.
    When you talk about "Different" locations for the "Main".
    Will there be more that one "Main"?
    Perhaps a redacted copy of the Main and too where the "Other" workbook will be located.
    If you have many users using different workbook you need to specify the path.
    Also you need to determine the range you wish to copy to the "Main".
    This will help us get you started.
     
  3. manukit

    manukit Thread Starter

    Joined:
    Jan 1, 2013
    Messages:
    12
    Hello,

    For Ex:- "MAIN" will be located in C drive. (There are no other worksheets, "MAIN" will be the master file")
    Other workbooks are located in F drive. (15 sub-ordinates who work, they will be saving the work in F drives.)

    I need to copy (from Col 2 to Col 8) entire contents which is in "Other Workbooks" to "MAIN". As soon as the worksheet gets filled in "Other" it has to get updated in "MAIN" also.

    Thanks,
     
  4. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Where will you want the data being copied go to in the "Main".
    Do you want the data from each workbook be copied to the next empty row?
    That it how do you have the "Main" workbook formated?
    If you can provide a copy.
    It makes it easier.
     
  5. manukit

    manukit Thread Starter

    Joined:
    Jan 1, 2013
    Messages:
    12
    Hi,

    Where will you want the data being copied go to in the "Main".
    Do you want the data from each workbook be copied to the next empty row?
    That it how do you have the "Main" workbook formated?
    If you can provide a copy.
    It makes it easier.


    Hello,

    I need the data to be copied in "MAIN" file name i.e., Workbook in any sheet, but need to be pasted only in one location.

    Yes. I need to be copied to the very next row which is empty from all the users.

    Same way, how the workbook is formatted. I have it in the MAIN as well.

    Please see the attachment, i have attached 2 files name : "MAIN" & "Other Workbook".

    Thanks.
     

    Attached Files:

  6. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    Thanks for the workbooks.
    In you previous post you mention that you wanted column 2 thru 8 copied to the main.
    In the main workbook you have it formated the same as the "OtherWorkbook".
    With this example I'll now assume that you want all of the data in the Otherwokbook copied to the Main.
    Now will the "Otherworkbook" data be cleared after you copy it too the "Main"?
    If not how would you determine what need to be copied to the Main after you've already copy the data.
    What is the criteria you look for in the "Other Workbook" to tell you it needs to be copied?
    As you see I need the thought process to determine the action of the code. The open/copy is not the hard part.
    As for the "OtherWorkbook" how would you know which book needs to be open/copy. Will you want to open all of them 1 by 1? Will they have their own name? Will they all be in the same folder on the F drive?
     
  7. manukit

    manukit Thread Starter

    Joined:
    Jan 1, 2013
    Messages:
    12
    Hi Charles,

    The formatting part: Both the books i.e., "MAIN" as well as "Other" will be having the same format".

    Data to be copied: I need the data from "Other" to "Main" to be copied as they enter in the worksheet. (Col 2 - Col 8). I don't require the data to be deleted from "Other" as soon as i copied it.

    Path: "MAIN" will be located in C drive.
    "Other" will be located in F drive. (Even the file name will be different for 15 persons, but it will be saved on same folder.)

    Criteria: for ex:- 01/03/2013 i have worked some data and saved in "other". Now, i need to pull the data from "other" stating from the given date "01/03/2013" as criteria & the data has to be saved in separate file called "MAIN".

    This process has to be done regularly on daily basis. So, here i can do is that i have to daily create new excel file called "MAIN". So that i can copy the file "MAIN" once it is done for the day in different location as a safe vault.

    Copy manner: The "Other workbooks" can be copied either by file name or alphabetical order.

    I need to the data of "Other" to be copied straight a way from their worksheet to "MAIN" as soon as they enter in "Other".

    All the Other workbooks will be in saving in same folder. But with different names,

    Please let me know if you have any queries.

    Thanks..
     
  8. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    HI,

    For the criteria you specified the Dates will be used.
    Will you lets say use 01/20/2012 as the date to pull from "Other".
    Will you use the same Date at any other time?(If you do we'll have a problem)
    In the Main file what would you do to say which Date too pull?
    Do you want a Form to use to input the date you wish to pull?

    I currently have code to pull the "Other" workbooks.
    In my code I specify the "Other" workbooks will be in the same folder.
    It will go thru the folder and copy the data from each "Other".
    To use this code they need to be in this folder and no other workbook should be in it.
     
  9. manukit

    manukit Thread Starter

    Joined:
    Jan 1, 2013
    Messages:
    12
    Yes. exactly i need to pull the data from the given date on daily basis. (It has to auto generate date by date wise).

    The same date will not be used any other time.

    In the main file i require to pull the data of "the date which i specify" on that date what are all the data has typed i need to copy it either by row wise or col wise.

    Yes, if form would be there then it will be more useful.

    In the other folder there will be only 15 files of "other". Other than that i don't have it.

    Thanks.
     
  10. CDHarm

    CDHarm

    Joined:
    May 26, 2011
    Messages:
    255
    Hi,

    This is what I came up with.
    When you open the workbook you will need to change the path I currently have coded to your path.
    In the code you will see "MyOther" this is the folder that will hold the files you wish to retrieve the Data from.

    NO OTHER FILES SHOULD BE THERE. ONLY THE FILES YOU WANT TO RETRIEVE THE DATA FROM!

    You will also see "strExt = Dir("*.xls")"
    The files must be ".xls" it will not work if the files are ".xlsm".
    I remarked the code so that you can see what it does.
    If you run the code with a date and then later on reuse that Date you will duplicate the data in the Main sheet.
    To view the code "Right" click the tab in Main and select "View" code. This will take you to the code for that sheet.


    All you need to do is change the date in A1. This will start the Macro.
     

    Attached Files:

  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1083260

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice