+ Reply to Thread
Results 1 to 6 of 6

To search a specific value and paste its corresponding data

  1. #1
    Registered User
    Join Date
    07-26-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    13

    To search a specific value and paste its corresponding data

    Hi,

    One excel sheet has two worksheets "report" and "details"
    In details sheet, in column A we have code numbers and in column B we have code names.
    In report sheet, in column A we will have code numbers same as in details sheets column A(not all may be present). and some other fields.
    My requirement is to write a code which will insert a column B in sheet "report" and search for code numbers in "details" sheet and if code number is found then its corresponding value in Column B of sheet "details" should be copied and pasted in "report" sheet in column B against the correct code number.

    I have written the below code not sure where i am going wrong as only few rows are getting pasted properly:

    Sub CodeToColumn()
    Dim names As Range, name As Range, values As Range, value As Range

    Set names = Worksheets("Details").Range("A2:A" & Worksheets("Details").Range("A" & Rows.Count).End(xlUp).Row)
    Set values = Worksheets("Report").Range("A2:A" & Worksheets("Report").Range("A" & Rows.Count).End(xlUp).Row)

    For Each name In names
    For Each value In values
    If name.value = value.value Then
    Worksheets("Details").Range("B2" & name.Row & ":B" & name.Row).Copy Destination:=Worksheets("Report").Range("B2" & value.Row & ":B" & value.Row)

    End If
    Next value
    Next name
    End Sub

    For now i am manually creating column B in "report" sheet.

    Thanks,
    Archies

  2. #2
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: To search a specific value and paste its corresponding data

    Hi,

    I think the same can be done through VLOOKUP or INDEX+MATCH formula, but if you have any restriction using formula than try below code:

    Please Login or Register  to view this content.
    Regards,

  3. #3
    Registered User
    Join Date
    07-26-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: To search a specific value and paste its corresponding data

    Thanks. It worked. But what mistake i was making in my code? If i you can help me to figure out that then in future i can such silly mistakes.

    Regards,
    Archies

  4. #4
    Valued Forum Contributor
    Join Date
    04-09-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2010
    Posts
    391

    Re: To search a specific value and paste its corresponding data

    Hi,

    Revise your code with the one below:

    Please Login or Register  to view this content.
    Regards,

  5. #5
    Registered User
    Join Date
    07-26-2012
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: To search a specific value and paste its corresponding data

    Figured out my mistake. Thanks

  6. #6
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,165

    Re: To search a specific value and paste its corresponding data

    Please Login or Register  to view this content.
    Last edited by davesexcel; 11-16-2014 at 09:46 AM.

+ 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] Search columns in specific sheets and paste values on sheet1
    By amar05 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-06-2014, 11:19 AM
  2. [SOLVED] Search for specific word in excel and paste it in the next column(not sure how to start)
    By onbeillp111 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-17-2013, 03:54 PM
  3. Search document for rows containing a specific word, cut/paste into new sheet
    By mea02300 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2013, 08:44 AM
  4. Search, Copy and paste specific field from one workbook to another
    By CARRpeter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-10-2012, 11:16 PM
  5. Replies: 3
    Last Post: 05-05-2010, 09:15 PM

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