More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Victor Lan
Expertise
I use Excel at work and can assist you in manipulating and filtering data, creating and using formulas, pivot tables, and writing VBA to solve problems and automate processes. I'm willing to research and find a solution for almost anything in Excel for you.
Experience
3 years of professional Excel experience. I currently work for a global investment bank and our team is responsible for certifying the daily profit and loss of the trading desk. Excel is one of the primary tools used at work.
Organizations
Member of Toastmasters International.
Education/Credentials
BSc Banking and Finance (First Class Honours), University of London.
Diploma in Electronics, Computer and Communication Engineering, Singapore Polytechnic.
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > compare, match and copy between sheets
Excel - compare, match and copy between sheets
Expert: Victor Lan - 11/7/2009
Question
Hi
sorry to email you directly previously..
below is what im trying to do
If cell H of Worksheet A = cell E of Worksheet B (contain words)
and
If cell J of Worksheet A = Cell H of Worksheet B (contain numbers)
and
If cell K of Worksheet A = cell I of Worksheet B (contain numbers)
then
copy cell O of Worksheet A to cell L of Worksheet B (contain numbers)
(put in other words
when a row in column H,J,K of Worksheet A = a row in column E,H,I of Worksheet B, copy that row's column O of worksheet A to column L worksheetB)
e.g. if H3, J3, K3 of worksheet A = E5, H5, I5 of worksheet B
then copy O3 of worksheet A to L5 of worksheetB
the macro is to match and copy for the whole worksheet of A and B
Data from worksheet A is only to be used once
I hope i provided enough details. Im new to vba. pls help. Thanks
______________________________________________________________
Below is what I got from one the experts. but it doesnt seems to copy anything (maybe I phased mine question wrongly.. hence i have rewrite the above problem).
Sub CopyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow As Long
Set sh1 = Worksheets("Worksheet A")
Set sh2 = Worksheets("Worksheet B")
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
j = (i - 2) * 4 + 1
If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
sh1.Cells(i, "O").Copy sh2.Cells(j, "L")
End If
j = j + 4
Next
End Sub
Answer
Hi nsy,
The VBA code given is working fine. However, your description of the problem is not too clear.
I have amended the code slightly.
Sub CopyData()
Dim sh1 As Worksheet, sh2 As Worksheet
Dim j As Long, i As Long, lastrow As Long
Set sh1 = Worksheets("Worksheet A")
Set sh2 = Worksheets("Worksheet B")
lastrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 2 To lastrow
j = i + 2
If sh1.Cells(i, "H").Value = sh2.Cells(j, "E").Value And _
sh1.Cells(i, "J").Value = sh2.Cells(j, "H").Value And _
sh1.Cells(i, "K").Value = sh2.Cells(j, "I").Value Then
sh1.Cells(i, "O").Copy sh2.Cells(j, "L")
End If
Next
End Sub
Please check if it works as you wanted.
If not, kindly send me your file so that I can help.
Thank you.
Regards,
Victor LAN
Add to this Answer Ask a Question
|
|
