+ Reply to Thread
Results 1 to 12 of 12

Macro to move cells in 2 rows after word criteria to specific cells in other sheet

  1. #1
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    Hi I have below criteria for move from sheet 3 to specific cells in sheet 2 and sheet 3.
    Its a list of words and i want the word coming after to be copied and moved.
    Example i have fullName: to B5 Sheet 2 . It had to search after fullName: an copy the word after the : to B5
    fullName: Bo olsen example it have to copy Bo Olsen to B5 in sheet 2.
    Where the words can be is variant thats why i need to search for it.

    Below are the words i need to search for and copy after the :

    Please have a look and see. I also put a test sheet which can be good to play with and also understand it.

    Thanks in advance
    Sincerely
    Abjac

    these are the creterias:

    All data is in row 1 and 2 sheet 3
    And it goes from column A to DD

    All after the : have to be copied.

    fullName: to B5 Sheet 2
    contactPhone: to B9 Sheet 2
    callMeetingDate: to B11 Sheet 2
    contactEmail: to B8 sheet 2
    jobFunction:to B6 sheet 2
    CTRY:DK to B13 sheet 2
    accountPhone:to B20 sheet 2
    accountName:to B12 sheet 2
    accountEmail: to B16 sheet 2
    accountVatNumber:to B21 sheet 2
    accountRangeEmployees:to B18 sheet 2
    accountRangeRevenue:to B17 sheet 2
    accountIndustry:to B24 sheet 2
    accountSubIndustry:to B25 sheet 2
    accountWebSite:to A6 sheet 3

    BillingStreet:to A5 sheet 3
    BillingCity:to B5 Sheet 3
    BillingPostalCode:to C5 sheet 3
    accountWebSite:to A6 sheet 3
    Next is special want to merge the cell after interactionNote: with the next 5 cells in same row

    interactionNote:\ Merge this cell and the next 5 cells in same row to first cell and copy all to A7 in sheet 3
    Attached Files Attached Files

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    Try:
    Please Login or Register  to view this content.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    HI Mumps1. It seems like it works really good. Some corrections i did. But i cant get the callMeetingDate: to work it should copy over to B2 in sheet 2.
    Also interactionNote:\ ( Special Merge this cell and the next 5 cells in same row to first cell and copy all to A7 in sheet 3 )
    So after interactionNote the text in the next 6 cells in same row have to be merged to the first cell after interactionNote and after
    copied to A7 in sheet 3.
    Please have a look
    Here is the code with other few corrections which was not correct.

    Please have a look
    Sincerely
    Abjac

    Please Login or Register  to view this content.

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    In your original post, you said:
    callMeetingDate: to B11 Sheet 2

  5. #5
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    HI Thats true and the code seems correct but it dont copy the date to B11. Have tried play with the format- In my test sheet you can see this date in F2 sheet 3. Ahh i think i found out why. The callMeetingDate is 2 places. In row 1 CN1 with no data, and in row 2 F2 with data. I solved it by replace the callMeetingDate with "" in a macro ( see below)


    The last missing is the interactionNote:
    Also interactionNote:\ ( Special Merge this cell and the next 5 cells in same row to first cell and copy all to A7 in sheet 3 )
    So after interactionNote the text in the next 6 cells in same row have to be merged to the first cell after interactionNote and after
    copied to A7 in sheet 3.

    Thanks
    Abjac

    macro with replace

    Please Login or Register  to view this content.
    Last edited by abjac; 03-19-2018 at 04:25 PM.

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    "interactionNote" is in cell CW1. Do you want to join cells CX1 to DC1? Cells in Cz1 to DC1 are currently blank.

  7. #7
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    Hi. Mumps1 the word interactionNote are actually in both row 1 and 2. As you saw i made so i delete it in row 1. But if you can do so this dont matter. I would like to have included 3 extra cell yes. Because if much more text it will in the sheet come in these cells as well. So yes would like that.
    Thanks
    Sincerely
    Abjac

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    Try:
    Please Login or Register  to view this content.

  9. #9
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    HI Mumps1 and thanks. Just tried and it seems that some works well. But it took away the text in CW1 and only pasted and merged the remaining from the other cells-. So the first data missing when its copied to A7. Quite weird actually according to the code.
    Sincerly
    Abjac

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    Replace this line:
    Please Login or Register  to view this content.
    with this line:
    Please Login or Register  to view this content.

  11. #11
    Forum Contributor
    Join Date
    07-28-2012
    Location
    madrid
    MS-Off Ver
    Excel 2010 at work excel 2016
    Posts
    1,102

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    HI Mumps1 . Sorry actually i should have seen that, but working also sorry. I just checked it an it works really perfect. Thanks alot for your efford. Really great.
    Have a nice day.
    Sincerely
    Abjac

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010, 2013
    Posts
    7,815

    Re: Macro to move cells in 2 rows after word criteria to specific cells in other sheet

    You are very welcome.

+ 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] Macro to clear cells in range & move up if certain cells meet criteria
    By samder68 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-09-2016, 10:52 PM
  2. [SOLVED] Macro for copying rows with cells with specific values from 1 sheet to another
    By Rizla21 in forum Excel Programming / VBA / Macros
    Replies: 40
    Last Post: 12-19-2013, 11:14 AM
  3. Replies: 12
    Last Post: 02-21-2013, 12:55 PM
  4. Move Rows of Data from a Specific Column (Based on Matching Criteria) to a new Sheet
    By jordan1214 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-21-2013, 02:30 AM
  5. HELP! Macro to Copy specific cells from one sheet to another based on specific criteria
    By atriscritti in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-11-2012, 11:05 AM
  6. Macro to move rows, row by row, based upon specific criteria.
    By luke8182 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-20-2012, 01:05 PM
  7. Macro to move entire row if cell contains a specific word and moved to sheet 2
    By Astarte in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2011, 09:21 AM

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