+ Reply to Thread
Results 1 to 5 of 5

Transfer info based on matching sheets

  1. #1
    Registered User
    Join Date
    06-15-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    21

    Talking Transfer info based on matching sheets

    Hello! I am working with a workbook in excell that I want to use to transfer info onto another sheet. I want to write a macro that will look at a cell in sheet 1 then compare it to the cells in sheet 2. If the value in sheet 1 matches the value in sheet 2 then I want excell to transfer that info from the selected cell on sheet one into the matching cell on sheet 2.

    Example

    transfer from Sheet 1: record no 1
    to
    sheet 2 : record no 1

    there are alot of record numbers and I only want the row to be transfered if the record no matches from sheet 1 to sheet 2.

    I hope I have not made that top confusing. Help please?

    Thanks
    Colleen

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Transfer info based on matching sheets

    Hi and welcome to the forum

    Why do you need a macro to do this, when a simple vlookup() will do it for you?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    06-15-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Transfer info based on matching sheets

    Hello and thanks for your reply! I am new to VBA programming so I hope you will fogive my lack of know how. I am using this to transfer information back and forth from autocad. I import a txt file into my excel sheet that has a lot of I formation and the only way to tell where the information should be is by these record numbers. When I updat the txt file I want to filter in the info based on record numbers. However I may not always have info for all of them at the same time so I only want excel to update the information I have avaliable. Is that how vlookup works or will I need seperate code?

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,745

    Re: Transfer info based on matching sheets

    =vlookup() is a regular non-VBA function that is used to search 1 column for a specified value/text, and then, if found, return info from the relevent row, in column "x"...

    =VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match
    so, =vlookup("xyz",A1:E10,3,0) translates into...

    Find xyz in the range A1:A10 (because it only looks in 1 column of data), in the over-all range A1:E10. If you find xyz, then give me the entry in the 3rd column (column C). the final 0 is to force an exact match rather than the closest match (1)

  5. #5
    Registered User
    Join Date
    06-15-2013
    Location
    usa
    MS-Off Ver
    Excel 2010
    Posts
    21

    Re: Transfer info based on matching sheets

    So I tried VLOOKUP() and it does move info around, however I cannot have a formula in my spreadsheet. Maybe if I better explain what I am doing it will help......

    I have blocks in AutoCAD that have attributes attached to them. I will be using many instances of these blocks in one project, but need to track each one individually. I pull the attribute values into a txt file then import that txt file into Excel and to track each block I give them a "record number" attribute so that each blocks information can be tracked seperately. Once the txt file is in excel I want to pull that information onto other sheets inside the workbook. I want a macro to look through the record numbers until they find a match from one sheet to the next and when they match I need this macro to pull the entire row of information related to that record number onto the other sheet. If there is no match the macro should put the unmatched values row in the first available blank row on the second sheet. Once the information is on the other sheet I need to be able to modify it so it cannot be a formula like VLOOKUP. Once the required information has been changed I want to transfer the changed information back into the sheet with the information from the txt file so I can upload it back into AutoCAD. Does that make more sense?

+ 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