+ Reply to Thread
Results 1 to 7 of 7

Copy specific columns from multiple workbooks into(append) a new sheet.

  1. #1
    Registered User
    Join Date
    12-31-2018
    Location
    Hyderabad, India
    MS-Off Ver
    2013
    Posts
    4

    Copy specific columns from multiple workbooks into(append) a new sheet.

    I am new to macros. My requirement is as follows. I have bunch of excels(2007 version) in a folder. Each excel may have one sheet. Each sheet contains a set of columns. Identify the column name in the Header row and copy the entire column in a separate sheet. Like this i have 6 columns that i need to identify and copy into them all in a separate sheet. Like this accumulate all the excels in that folder. column names may not be in the same order. Hence need to find a column based on the name given by business and copy only that column and append to result in a separate sheet. I wrote some, but stuck in the middle. Kindly help me if there is a way to move forward.

    Here is my program, still not complete. My idea is, 1). First iterate the folder and take each excel on sheet1. 2). Create a temporary workbook with sheet1 for appending columns here. 3.) Open each workbook and iterate header in sheet1. Once my column name matches, i want to take the entire column and append the column in that workbook in sheet1. 4.) After all workbooks are processed, save and close the workbooks.

    Please Login or Register  to view this content.
    Last edited by AliGW; 01-01-2019 at 03:04 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Excel 2016 (Win 10 - Work) & 365 Subscription (Win 10 - Home)
    Posts
    30,571

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Welcome to the forum!

    Rule #2 of our Forum Rules (link above) tells you how to add code tags to your code, which are required here. As you are new, I have done it for you this time. In future, please make sure you do it yourself. Thanks!
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office XP to Office 2016
    Posts
    1,627

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Do you have multiple workbooks in a folder and you want to combine a specific sheet of each workbook into a master workbook?

    Please attach a sample folder containing at least 3 sample source books and one master workbook with the settings where you want all the workbooks to be combined.
    Teach me Excel VBA

  4. #4
    Registered User
    Join Date
    12-31-2018
    Location
    Hyderabad, India
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Sure. i will do. Thanks for take care of my content. Next time i will make sure.

  5. #5
    Registered User
    Join Date
    12-31-2018
    Location
    Hyderabad, India
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Hi Imran, Attaching 4 work books. out these Summary workbook is the master book to copy the content from the workbooks.

    Copy the following columns from each workbook(of sheet1) into Summary workbook of sheet1

    1. ProjectNumber
    2. Beginning_time : Survey Channel = ^GetSurveyChannel()^ Beginning Time - ^GetSurveyChannel()=='Capi'?f('CAPICountry'):"Sydney"^ time
    3. OpenEndedsID : Open Endeds Respondent ID. This question stores unique id which each interviewer has to note on the OE Sheet.
    4. Q8 : Q8. Record the centre [SA] {QCenter}
    4. IntervwrDetails_ID : Nielsen Interviewer ID (Please fill below details.)
    5. StartTimeSlot : Interview Start Time Slot based on Beginning_time
    6. Project Name
    7. Columns fom main file

    These columns are not fixed, they may change the order in the sheet. Columns need not exist in each workbook, some times data may not exists.
    Attached Files Attached Files

  6. #6
    Forum Expert
    Join Date
    10-06-2017
    Location
    drevni ruchadlo
    MS-Off Ver
    old
    Posts
    1,248

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Try, this is the development of your code (without full error handling).
    If the locations or names of sheets, workbooks, etc. are not going to match, then there will be errors stopping the code.
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    12-31-2018
    Location
    Hyderabad, India
    MS-Off Ver
    2013
    Posts
    4

    Re: Copy specific columns from multiple workbooks into(append) a new sheet.

    Thanks a lot porucha . Really working great. i modified with my list of columns. This is really helpful to move further. i really appreciate your help in this regard.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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