+ Reply to Thread
Results 1 to 5 of 5

Search multiple workbook headers and copy column entries to another workbook

  1. #1
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Search multiple workbook headers and copy column entries to another workbook

    Hi guys,
    I've been trying to find something that will help me with this but haven't found anything I can manipulate to suit my needs.
    My problem is this:

    I have many workbooks in a folder, lets say c:\temp\book1.xls, book2.xls...book100.xls
    I have another workbook in c:\main.xls
    Each of the workbooks in c:\temp contain different columns in different orders, but they all have one consistant column header labelled 'Phase' in the first row (the column number is not the same however).
    main.xls also has a column labelled 'Phase'.
    Also the column may occur more than once in a workbook.

    What I'm looking for is a macro that I can run from main.xls, that will look through all of the workbooks in c:\temp, search the top row until it finds 'Phase' and copy all of the non blank entries in that column, and put them into the 'Phase' column in main.xls, one after another (essentially making one big list of all the phases from the workbooks in c:\temp.
    Additionally, if possible, i would like it to put the name of the workbook it found the entries in next to it.
    For example, main.xls would ideally look like this:

    Workbook Phase
    Book1 phase1
    Book1 phase2
    Book1 phase3
    Book2 phase3
    Book2 phase4
    Book3 phase1
    Book3 phase3

    Is anyone able to help me out? This would be MUCH appreciated as it would save me soooo much time!

    Thankyou!!!

  2. #2
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Search multiple workbook headers and copy column entries to another workbook

    Hi
    it's a it messy, but this might work:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Search multiple workbook headers and copy column entries to another workbook

    That's fantastic! Thanks!
    Theres one issue though. It copies all of the data across to the main spreadsheet, but it overwrites itself rather than listing down the columns?

  4. #4
    Forum Expert
    Join Date
    06-09-2010
    Location
    Australia
    MS-Off Ver
    Excel 2013
    Posts
    1,714

    Re: Search multiple workbook headers and copy column entries to another workbook

    hi
    it should copy the contents of each cell below a heading "Phase" to a new row in the master sheet - this bit of code should add 1 to the destination row each time a new phase is copied:

    PhaseLast = PhaseLast + 1

    or is the problem with the workbook? if you want the contents of the cell to read

    workbook1 phase 23

    try this variation:


    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    12-09-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Search multiple workbook headers and copy column entries to another workbook

    Nevermind, it was an error I made when I copied it over and made it suit.

    Thankyou so much!!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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