Consulting

Results 1 to 4 of 4

Thread: need help writing a code for copy and paste special (values)

  1. #1

    need help writing a code for copy and paste special (values)

    Hi there,
    I was wondering if someone could help me in writing a code for excel.

    If values in a cell A1 in sheet 1 is equal to values in cell A1 in sheet two then excel copy and paste values in whole row 1 in sheet 2.

    Explanation:

    I have set conditions that all data in different cells in sheet 1 is gathered in such a way which is linear (all data of sheet 1 in one row). but problem is each time for new information i have to copy and paste special data gathered previously so that information is not lost.

    Please help me write a code that do this for me.

    Thanx

  2. #2
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    This is simple and wont expand to include anything else or paste to other rows (as per your request)
    Sub Copy_It()
    If Sheets("Sheet1").Range("A1").Value = Sheets("Sheet2").Range("A1").Value Then
    Sheets("Sheet1").Range("A2:" & Range("A1").End(xlToRight).Address).Copy Destination:=Sheets("Sheet2").Range("B1")
    Application.CutCopyMode = False
    End If
    End Sub
    However, if you simply add a formula to sheet2 cells from B1 onwards it will simply display the contents of sheet1 if there is a match in the same row
    Put this in Sheet2 cell B1 and copy across =IF($A$1=Sheet1!$A$1,Sheet1!B$1,"")
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

  3. #3
    Thank you so much for you reply. however this is code is not working on my sheet. I have attached the screenshot here for better understanding. In invoice sheet as soon as i enter particular number lets say "724" it finds same in data base (another sheet) and take figures as i enter in invoice. problem is when i change the invoice number to enter next invoice for example "725" it changes all details of 724. for this i have to delete formulas in the row having invoice 724 by copying and pasting special.

    I need a code; when i run that excel search value of invoice (E9) in whole column B (where invoice numbers are) and then copy whole row which contain the same figure and paste special (values).
    Please if you can help!
    Thank you in advance.
    screen shot.jpg

  4. #4
    Moderator VBAX Guru Simon Lloyd's Avatar
    Joined
    Sep 2005
    Location
    UK
    Posts
    3,003
    Location
    The code I supplied works as per your original question, it shouldn't take you too much effort to adapt it, you should be looking to paste the details to the next available row in the results sheet (Google is your friend!)
    Regards,
    Simon
    Please read this before cross posting!
    In the unlikely event you didn't get your answer here try Microsoft Office Discussion @ The Code Cage
    If I have seen further it is by standing on the shoulders of giants.
    Isaac Newton, Letter to Robert Hooke, February 5, 1675 English mathematician & physicist (1642 - 1727)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •