+ 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
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,406

    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!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

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

    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
    2,143

    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)

Similar Threads

  1. [SOLVED] Copy specific columns from multiple workbooks to new workbook
    By nwix in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-03-2017, 10:48 AM
  2. [SOLVED] macro to copy data to specific columns in same workbooks
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 04-11-2017, 10:27 AM
  3. copy data from multiple sheet to master sheet with specific columns only
    By sinha.riteshabap in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-31-2013, 06:59 AM
  4. Macro to copy values from specific columns from different workbooks to one sheet
    By sksajid7 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 07-11-2013, 09:50 AM
  5. Replies: 8
    Last Post: 04-04-2013, 08:02 PM
  6. Replies: 1
    Last Post: 07-17-2012, 11:05 PM
  7. Macro to extract data from multiple workbooks, specific sheet, specific cells
    By crissandraauree in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2012, 03:54 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