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

[munimanis]

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

[Simon Lloyd]

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 SubHowever, 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,"")

[munimanis]

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.
20796

[Simon Lloyd]

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!)