+ Reply to Thread
Results 1 to 11 of 11

Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

  1. #1
    Registered User
    Join Date
    01-18-2013
    Location
    Santo André, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    5

    Question Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Hi! I just started with VBA since just recording macro functions are not helping me anymore :P

    So, what I'm trying to do is to copy data from specific columns, that are in different order and in different worksheets, and produce a new sheet with the compiled information from all the worksheets.

    I need this because I receive a large number of sheets from different employees, and the sheets they complete have different column headers (on purpose) and it's a pain to manually copy paste everything everyday

    If you guys could be kind enought to add some coments on the code so I can better understand what I'm doing it would be very helpfull. That way I can have my problem solved and learn some VBA in the process ^^

    I Attached my workbook.
    Pasta1.xlsx


    Hope you guys can help -- I'm losing my hope

  2. #2
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    See here. Maybe for you it is useful.
    Many thanks to all who have helped me.

  3. #3
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Aribe,
    What you want to copy and on what condition is not clear as per the attached. You have 3 tabs. The plan3 tab has headers which appear to be a mixture of headers from the other two tabs.
    Please specifay which columns, from which sheet and to where you want to copy?

  4. #4
    Registered User
    Join Date
    01-18-2013
    Location
    Santo André, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Quote Originally Posted by AB33 View Post
    Aribe,
    What you want to copy and on what condition is not clear as per the attached. You have 3 tabs. The plan3 tab has headers which appear to be a mixture of headers from the other two tabs.
    Please specifay which columns, from which sheet and to where you want to copy?
    Hi! The plan3 tab specifies which headers from the first 2 plans should be copied and compiled with the complete info from the other sheets.

    So the condition is if the header matches the header from the master sheet, data should be copied.

    I'm uploading it again with english headers and the sheet in which all data shoul be compiled is called "master".

    I made this code to find the data, but is not working, and also, I couldn't make up a way that the data colected from sheed "Loc" stays bellow sheet "Syg" - it's always coping to the beggining of the master sheet, instead of under all the already collected data.

    Please Login or Register  to view this content.
    Thank you!
    Attached Files Attached Files
    Last edited by vlady; 01-22-2013 at 09:32 PM.

  5. #5
    Registered User
    Join Date
    01-18-2013
    Location
    Santo André, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Hi Anka!

    I tried to use you directions, but I couldn't find a way to refer to a column by its name in it..

  6. #6
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Okay.

    Try this. (this part sure you know).
    For your VBA-Try spreadsheet.
    Alt-F11. Insert>Module from the Menu bar. Paste the Code there. Alt-F8 to run the sub.

    Please Login or Register  to view this content.
    How? Easy! I am not able to do this, but ...
    I changed to your case, what I've learned here!

    Now it's easy to change to your original spreadsheet (Pasta1).
    Tell me if that's what you're looking.
    Last edited by Anka; 01-22-2013 at 10:17 PM. Reason: I forgot this "Worksheets ("Master"). Enabled" .

  7. #7
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Adding sheet in case ..
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    01-18-2013
    Location
    Santo André, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Almost there!

    The thing is, I also need to add the data from both "Loc" and "Syn" to my Master sheet. I tryied to add the code with the different sheet name, but it ends up copying data over my other results.. Maybe if there was a code to simply make the data copied from the Syn to be at the end of the Master results obtained with the Loc one.

    This is what I tryied:

    Please Login or Register  to view this content.
    THank you!!

  9. #9
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Try the new sheet.
    I slightly modified your master sheet. I put a button to update who (1) delete the old data and (2)new data restore (if new data have been added to sheets). And code (a simple change, but I'm sure there are more professional forms to do so). But if you want you can restore as it was.
    Tell me if that's what you want. Then I tell you where I found the code.
    Attached Files Attached Files
    Last edited by Anka; 01-24-2013 at 05:39 PM.

  10. #10
    Registered User
    Join Date
    01-18-2013
    Location
    Santo André, Brazil
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    No really.. Actually, the last try was more close to it.

    Let me explain myself better.
    I need to:
    1) Find a certain colum name (one that is on the Master sheet)
    2) Copy all data from the Syg and Loc Sheets that are under this particular colum
    3) Paste all this data into a single colum on the Master

    I uploaded a sheet colored, so you can see better what I need.

    Thank you for all your help ^^
    Attached Files Attached Files

  11. #11
    Forum Contributor Anka's Avatar
    Join Date
    08-25-2012
    Location
    Lisbon
    MS-Off Ver
    Excel 2016
    Posts
    174

    Re: Copy Specific Columns (Named ones) from different sheets to a new "master" sheet

    Hi

    Now I understand exactly what you want.
    You can find the solution in the new sheet.
    Attached Files Attached Files

+ 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