+ Reply to Thread
Results 1 to 8 of 8

Copy info from different workbooks into a consolidating file

  1. #1
    Registered User
    Join Date
    09-25-2020
    Location
    vienna
    MS-Off Ver
    365
    Posts
    19

    Copy info from different workbooks into a consolidating file

    Dear Members,

    I would like your kind help with the following. I have a consolidate file that collects information from several xlsm files. The xlsm files are in a subfolder with the name of a Project. Inside each Project subfolder there is a file called Pn.xlsm.
    I have been strugling on making a code that updates automatically on the Consolidate file, all changes done in each Pn file.
    Basically what Im looking for is a code that copy the range B5:N21 of the Pn.xslm file on each project folder and paste it in a specific range inside Consolidating xlsm, depending on the Project.
    For example:
    The location of the Consolidating file is: 'E:\Project Control\[Consolidate.xlsm]
    The location of Project XXXX is : E:\Project Control\Project XXXX\[Pn.xlsm] and the information should be updated on the range B15:N31
    The location of Project YYY is : E:\Project Control\Project YYYY\[Pn.xlsm] and the information should be updated on the range B36:N52
    The location of Project ZZZ is : E:\Project Control\Project ZZZZ\[Pn.xlsm] and the information should be updated on the range B57:N73

    I hope the examples are clear!
    Attached Files Attached Files
    Last edited by papasioux; 05-18-2021 at 05:33 AM. Reason: Updated files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: Copy info from different workbooks into a consolidating file

    We would have to see samples of both the Pn and the Consolidate file to write appropriate code to carry out this update. See yellow banner above for instructions.

  3. #3
    Registered User
    Join Date
    09-25-2020
    Location
    vienna
    MS-Off Ver
    365
    Posts
    19

    Re: Copy info from different workbooks into a consolidating file

    Thanks for replying! I have added both files as you suggested

  4. #4
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: Copy info from different workbooks into a consolidating file

    The two files do not look like the data you outline in your first post. It may be because some data is suppose to update via links we don't have.

  5. #5
    Registered User
    Join Date
    09-25-2020
    Location
    vienna
    MS-Off Ver
    365
    Posts
    19

    Re: Copy info from different workbooks into a consolidating file

    Yeah, I change it since I was getting a problem with that way. I managed to make a macro that opens both files but the problem I got into was that the numbers were correct as long as I keep the books open. for avoiding keeping references to other books I am trying to get the macro that copy from one book to another and then to copy again the values as I explained in the new post

  6. #6
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    1,909

    Re: Copy info from different workbooks into a consolidating file

    It's clear where you want to enter data into the consolidate file. But the Pn file looks just like the consolidate file. It's not clear where you want to pull data from. Shouldn't there be just one are in each Pn file that gets pulled into Consolidate file that will house many project statuses?

  7. #7
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,419

    Re: Copy info from different workbooks into a consolidating file

    .........................
    Last edited by jindon; 05-19-2021 at 09:05 PM.

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,419

    Re: Copy info from different workbooks into a consolidating file

    Quote Originally Posted by papasioux View Post
    Basically what Im looking for is a code that copy the range B5:N21 of the Pn.xslm file on each project folder and paste it in a specific range inside Consolidating xlsm, depending on the Project.
    For example:
    The location of the Consolidating file is: 'E:\Project Control\[Consolidate.xlsm]
    The location of Project XXXX is : E:\Project Control\Project XXXX\[Pn.xlsm] and the information should be updated on the range B15:N31
    The location of Project YYY is : E:\Project Control\Project YYYY\[Pn.xlsm] and the information should be updated on the range B36:N52
    The location of Project ZZZ is : E:\Project Control\Project ZZZZ\[Pn.xlsm] and the information should be updated on the range B57:N73
    This is to update the corresponding range based in the Project name(s) found in Col.A.
    Please Login or Register  to view this content.

+ 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. Importing file as temp and copy paste info into main file!!! HELP PLEASE
    By cory0789 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-05-2015, 02:01 PM
  2. [SOLVED] Copy info from variable workbooks to a masterfile then loop
    By batuh in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-09-2014, 03:51 PM
  3. Replies: 0
    Last Post: 03-24-2014, 09:23 PM
  4. Replies: 2
    Last Post: 02-10-2014, 11:52 AM
  5. [SOLVED] Consolidating files (copy from 1 excel file to another excel file)
    By city in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-19-2012, 03:39 PM
  6. Summing respective fields from Multiple workbooks to one Consolidating File
    By dawnmau in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-08-2011, 05:52 PM
  7. Consolidating Two Lists of Info
    By maogdamian in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-27-2010, 05:03 PM

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