+ Reply to Thread
Results 1 to 23 of 23

Macro to search workbooks in a folder for a specified column and paste in another workbook

  1. #1
    Registered User
    Join Date
    04-08-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Macro to search workbooks in a folder for a specified column and paste in another workbook

    Would like a macro that will do the following:

    - Go into a specified folder (ie. C:\data) which contains multiple workbooks
    - search each workbook for a column titled SCIN
    - copy the cell labelled SCIN and 60 cells below it to a new workbook (summary workbook)
    - repeat the process in all the workbooks until all of the SCIN columns have been located and copied into the summary workbook

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Do you want them one under the other or side-by-side?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello maximumkai,

    Here is one solution. This macro will examine all Excel workbook files (any file matching a ".xls*" pattern) in the folder "C:\Data". Each sheet in the workbook will be checked for a column named "SCIN". If the column is found then the data is copied to the summary sheet. The summary worksheet in the macro is named "Sheet1". This will need to be changed to match the name of your summary sheet.

    It is assumed that each worksheet in all the workbooks has column headers in row 1 and each has column labeled "SCIN" (case is ignored). If this not the case then data will not be copied because the macro can't find the data.

    This macro pulls the data from the workbooks while they are closed. This is a faster method than opening each workbook, copying then data and closing it, It also prevents flickering while the macro runs.

    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-08-2014 at 10:15 PM.
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    04-08-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Thanks Leith this works great for what I asked for but my requirement has changed slightly. Is it possible to have the Summary Workbook display the data it has pulled from the other workbooks in a separate column for each workbook?
    Also the SCIN column header is in Row 5 not in Row 1 (I failed to provide that detail initially)
    Please let me know how I can change the code to display the data in separate columns? Thanks again for your help. This is my first time using this forum and creating macros and I must say I am very impressed with the feedback you have provided.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello maximumkai,

    Does column 5 apply to the all workbooks including the summary workbook?

    You can easily switch the output to columns by changing one line in the macro.
    Please Login or Register  to view this content.
    This line of code is shown in blue in this For Next block...
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-08-2014
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2013
    Posts
    3

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    I believe side-by-side would work best but putting them one under the other could work if this is less complicated.

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello maximumkai,

    Here is the revised macro. This will place the pulled data side by side on the summary worksheet. The data will be pulled from the source workbooks starting at row 5. It also displays the workbook and worksheet being checked in the Excel Status Bar. This is a good visual indicator that the macro is working and not hung.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hi Leith,

    I am working in conjunction with maximumkai on this. I attached an example of what the source worksheet will look like that we are trying to extract data from. The highlighted cells are what we would like to extract. The issues I can't seem to find away around are

    - The data doesn't seem to copy into the Summary worksheet if there is text in the cell below the "SCIN" cell in row 5
    - The three rows of empty cells between blocks of data prevent the data from being extracted properly from the source worksheet

    In our case the number of rows required to be extracted from the source worksheet is the same every single time. Is there an easy way for the macro to just copy and paste the 40 cells below "SCIN" (including the SCIN title) into the Summary workbook regardless of whether the cell is empty, has text, or numbers? Thank you for your advice so far it's been a huge help.
    Attached Files Attached Files

  9. #9
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello [email protected],

    Thanks for introducing yourself. If you could also post a sample sheet from one of the files being read, it would help me in getting this right for you. If there is any sensitive information please either change it or delete it before you post.

  10. #10
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    I attached an example worksheet on my last post as an .xlsx file. Are you able to access that?

  11. #11
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello [email protected],

    Is the attached file the "Summary" workbook or one of the workbooks to be read?

  12. #12
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    It is one of the workbooks to be read.

  13. #13
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello,

    It looks like your column headers occupy 2 rows. My assumption is it is the same for all other workbooks to be read, correct?

  14. #14
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Yes that is correct.

  15. #15
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello,

    Still working on why the data is coming up zeroes.

  16. #16
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello [email protected],

    This macro works now on the example workbook you posted. Try it out on your workbooks and let me know the results.
    Please Login or Register  to view this content.
    Last edited by Leith Ross; 04-10-2014 at 04:13 PM. Reason: Added Some Comments to the Code

  17. #17
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    It works! Sorry for the delayed reply it took me a little while to realize there was a naming discrepancy with some of my other files. Thanks a million it would have taken me weeks if not longer without your help.

    Sincerely,

    Ian

  18. #18
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello Ian,

    You're welcome. Good to know it is saving you time and it is working as expected.

  19. #19
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Do I need to do anything else to say that this thread is solved?

  20. #20
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello Ian,

    No, I have already taken care of it.

  21. #21
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hi Leith,

    I have come across another small problem. If the worksheet in the source workbook that I am trying to copy from has a name with a space in it, the macro seems to skip over that workbook. A fair amount of the source workbooks have the first sheet named "Material Balance". Any ideas?

    In addition, after the macro copies the SCIN column, is it possible to copy the Cell A:1 in each source worksheet above it's respective SCIN Column. Cell A:1 is the title of the worksheet therefore having it above each column would be nice to keep track of where each column came from in the Summary workbook.

  22. #22
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Hello Ian,

    The naming rules for ADO only allow letters, numbers, and the underscore character. Anything else is considered illegal, even spaces. Worksheet names and column headers that do not follow this rule be not seen in ADO.

    Getting the data from the workbooks when closed is faster than opening them one by one and reading them. I will see if I can work around the problem.

  23. #23
    Registered User
    Join Date
    04-09-2014
    Location
    Canada
    MS-Off Ver
    Excel 2013
    Posts
    14

    Re: Macro to search workbooks in a folder for a specified column and paste in another work

    Ahh ok that makes sense. Alright thank you.

+ 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. Macro to cut, paste, search, repeat between two workbooks; request for help
    By wallabumba in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2014, 10:58 AM
  2. [SOLVED] Macro that Copy/paste a column to Notepad and Allows me to Save As in a specified folder
    By Nate87 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-16-2013, 05:12 PM
  3. search for a file in a folder based on data, copy and paste it in another folder
    By kboy1289 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-05-2013, 11:47 AM
  4. copy & paste data from multiple workbooks to new workbook in a folder
    By Ignesh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-01-2012, 03:11 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