+ Reply to Thread
Results 1 to 10 of 10

Open multiple workbooks based on cell values and copy and paste information

  1. #1
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Open multiple workbooks based on cell values and copy and paste information

    Hi,

    I am really sorry that I am asking a question without making any progress on that but I really couldn't find anything on this or the methods I tried all failed.We have about 160 different workbooks (one for each business unit) stored online. Staff enters information about their weekly revenue and expenses and here at head office I collect that information and consolidate them.

    What I am trying to do is that;

    1.) Create a master Workbook with ~160 worksheets (One worksheet for each unit) named exactly the same with other workbooks

    2.) And macro can pull the information from related files stored in a certain folder

    It is very much like another members problem but I am not sure why I cant get that code working for me?

    http://www.ozgrid.com/forum/showthread.php?t=158145I

    just spent hours on this and I am sure for a pro it would have an easy answer.

    I attach the workbook exactly explains what I am trying to achieve.

    I would really appreciate any help.

    Thanks in Advance.

    Egemen

    P.S. Also posted the same question on another forum and in case I get an answer there I will update it here

    http://www.mrexcel.com/forum/excel-q...ml#post3785429
    http://www.ozgrid.com/forum/showthre...d=1#post710760
    Attached Files Attached Files

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    re: Open multiple workbooks based on cell values and copy and paste information

    'WORKBOOKS TO SHEETS
    Here's a base macro for collecting data from all files in a specific folder. This version copies the sheet in as a whole.The parts of the code that need to be edited are colored to draw your attention.
    Last edited by JBeaucaire; 12-27-2019 at 03:52 AM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    re: Open multiple workbooks based on cell values and copy and paste information

    Hi Jerry,

    Already loving your web page and thanks for the info.

    Your code would definitely work for me. Just tested that and does exactly what I needed apart from all my files will be password protected so it needs to somehow enter the password to open workbooks and also it seems like it keeps running. Unless I press and hold esc key the code dont stop running. Not sure but might be due to Loop process? as when I stop the code it just highlights a line before Loop in debug mode.

    I havent tried it for workbooks with multible tabs.

    Thanks again.

  4. #4
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    re: Open multiple workbooks based on cell values and copy and paste information

    Hi Jerry,

    I think it was running for a long time due to few of my files having multiple tabs... Now I removed them and it seems working fine...

    If I can add a password for each file to open that would do the trick but I am not sure whether it would be possible as the code simply opens every file in the folder rather than specified files. As every file will have a different password.

    Thanks,

    Egemen

  5. #5
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open multiple workbooks based on cell values and copy and paste information

    Yes, you can add a password:

    Please Login or Register  to view this content.

    This macro is opening every file in a folder. If you need to create a table of workbook names and passwords:

    1) That wouldn't be a very "secure" environment... a sheet listing all your passwords is never a good idea.
    2) That really would be a different macro.

  6. #6
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Open multiple workbooks based on cell values and copy and paste information

    Hi Jerry,

    Thanks for your help and I really appreciate it.

    I think I have to try and build something then ask for help as it is not fair to ask people to do it for me from scratch. I found lots of threads and usually good at adapting codes etc but cant work this one out http://www.excelforum.com/images/smilies/frown.gif

    I will update here if I can manage to create something that I need help or if i have a final solution.

  7. #7
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Open multiple workbooks based on cell values and copy and paste information

    One last question.

    How can I amend your code so it works with workbooks with multiple worksheets and copies sheets called i.e. "Source" and this worksheet name will be exactly the same for all workbooks.

    Also just for ease of use can I put a reference to a cell for the directory rather than keeping it in the code?

    Thanks again.

    And I found your website very useful, full of great codes :-)

  8. #8
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Open multiple workbooks based on cell values and copy and paste information

    I also found this code elsewhere works in similar way to Jerry's but it takes copies all worksheets from source workbooks into target workbook.

    Same fixes....

    How can I amend your code so it works with workbooks with multiple worksheets and copies sheets called i.e. "Source" and this worksheet name will be exactly the same for all workbooks.

    Also just for ease of use can I put a reference to a cell for the directory rather than keeping it in the code?

    Please Login or Register  to view this content.
    Last edited by JBeaucaire; 04-23-2014 at 12:14 PM. Reason: corrected CODE tags. Use the EDIT button to make corrections in your posts.

  9. #9
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Open multiple workbooks based on cell values and copy and paste information

    I would think these are the edits to make my macro copy only from the SOURCE sheet in each workbook opened:

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    01-07-2013
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Open multiple workbooks based on cell values and copy and paste information

    Hi Jerry,

    Fantastic and thank you very much for your help....

    This works like a treat... And while trying to build up this learnt from you too :-)

    Just to sum it up for the others who are trying to do something similar .... Hope / Sure this will help others for years.... (Since I started using Excel Forum I find solutions to my problems from posts going back to early 2000s)

    ... This code will loop through a specified directory in your drive and open all .xlsx files one by one and copy and paste all sheets called "Source" to your master file then close workbooks. And move all the files into a folder called "Imported" ...

    1.) Reference to your path is in B12 of Active Worksheet of this workbook. This can be changed
    2.) Worksheets to be copied in source workbooks are named "Source" This can be changed

    Please Login or Register  to view this content.
    Link to Original Code is - https://sites.google.com/a/madrocket.../wbs-to-sheets

    Warmest Regards,

    Egemen
    Last edited by JBeaucaire; 12-27-2019 at 03:53 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Open Multiple Workbooks, Record names, Copy paste to Active Workbook, Close the Workbooks
    By vba_madness in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 01-24-2013, 06:09 AM
  2. [SOLVED] Macro to copy,paste and group information based on value in a cell
    By gullit in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-14-2012, 05:32 PM
  3. Copy/pasting values based on a changing day (multiple workbooks)
    By evilgrinners in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-16-2012, 10:05 AM
  4. Copy & paste between open workbooks
    By Robotacha2010 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-16-2010, 11:13 AM
  5. Open multiple files from a folder based on cell information?
    By bshape1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-03-2008, 07:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1