If cell 'a' matches cell 'b' then copy cell 'd' to cell 'e'

M

Marie

Sounds like a simple if/then statement right? Can't get it to work for
anything. I am going from worksheet to worksheet but that should not
present a problem.... I've even tried it without changing worksheets. Open
to any and all suggestions.....I basically want to automate a copy/paste
funtion for cells that match from worksheet to worksheet.........
Marie
 
G

GTVT06

This worked for me... I just tested it on row 1, but it can be
adjusted for your needs

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A1").Value = Range("B1").Value Then Range("D1").Copy
Range("E1")

End Sub
 
M

Marie

Ok...... basically, I assume you answered my question..... problem is, I
don't know what to do with the answer. Would you please give 'idiot' proof
directions on how to proceed? Do I copy/paste everything from 'private
sub..........to end sub' into a cell and adjust it for my ranges? Then copy
it to each cell I need it to put the answer in? Do I put it somewhere in the
worksheet to execute under a macro key? I'm sorta lost here. I know just
enough to be dangerous. Please help out.
 
J

JLGWhiz

Since you are working between sheets, you will have to qualify your range
object statements by specifying the sheet for each object.

Sub cpynpst()
Set wks1 = Worksheets("Sheet1")
Set wks2 = Worksheets("Sheet2")
With wks1
If .Range("A1") = .Range("B2") Then
.Range("D1").Copy .Range("E1") ' <<<copies in same WS
End If
End With
If wks1.Range("A1") = wks1.Range("B2") Then
wks1.Range("D1").Copy wks2.Range("E1") '<< cpy to 2nd WS
End If
End Sub

When working between sheets, it is a good practice to qualify all of your
range objects (cell references and ranges) with their applicable worksheets.
That way VBA won't have to guess which one you really want to address.
 
J

JLGWhiz

Sorry, I should have given you the answer to your question. The code that I
provided would be pasted into the standard module. Press Alt + F11 to open
the VB editor. If the code window is dark, then from the VBA menu bar
Insert>Module. Then copy and paste the code...But, the code I provided is
only for illustration. The code that GTVT06 provided was for a Worksheet
change event and would go in the code module behind the active sheet. But I
don't think you were looking for either of those answers. I think the
answere that you want is:

=IF(Sheet1!a1 = Sheet1!b1, d1, "")

and this formula would be put into cell E1 of sheet 2. It says if a1 = b1
on sheet 1 then cell e1 = cell d1 of sheet 2.

If this doesn't explain what you want to know, then try explaining the
problem again.
 
M

Marie

If this works I can't thank you enough. I have 7 spreadsheets that have a
column in common but the information relating to that column is different on
each sheet. I need to combine them all to one sheet and of course the colums
are not exactly alike......<grin>
 
M

Marie

What I am looking to do, and I don't think I made myself quite clear enough,
is on a separate worksheet from all the others..........

Have if cell sheet 1 a1 = cell sheet 9(this is the working sheet) b2, then
copy cell sheet 1 c1 to cell sheet 9 d2.

Now I've probably thoroughly confused you........ I want to automate
matching the cell from one sheet to another sheet and if they match then take
the information from a corresponding cell and place it in the matching sheet
in another cell.

Hope this clears it up a little.

Marie
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top