How do I write the visual basic code for the following.
If the value in the cells in column A (sheet 1) matches the value in column
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

Many thanks, in anticipation.

Hi,

I didn't quite follow your logic

A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

This would overwrite the value in column A which I assume you don't want to
do so instead this writes to columns B,C & D in sheet 2.

This should work as worksheet code or in a module

Sub copyit()
lastrows1 = Sheets("Sheet1").Range("A65536").End(xlUp).Row
lastrows2 = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Dim MyRangeS1, MyRangeS2 As Range
Set MyRangeS1 = Sheets("Sheet1").Range("A1:A" & lastrows1)
Set MyRangeS2 = Sheets("Sheet2").Range("A1:A" & lastrows2)
For Each c1 In MyRangeS1
For Each c2 In MyRangeS2
If c1 = c2 Then
c2.Offset(0, 1).Value = c1.Offset(0, 13).Value
c2.Offset(0, 2).Value = c1.Offset(0, 14).Value
c2.Offset(0, 3).Value = c1.Offset(0, 15).Value
End If
Next
Next
End Sub

Mike




"thomsonpa" wrote:

How do I write the visual basic code for the following.
If the value in the cells in column A (sheet 1) matches the value in column
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

Many thanks, in anticipation.

Thank you ever so much Mike, you were right about a and b. It is no wonder I
can never get my visual basic to work when I make such mistakes. Still, I am
learning all the time from this discussion group.

Your answer worked first time.

"Mike H" wrote:

Hi,

I didn't quite follow your logic

A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

This would overwrite the value in column A which I assume you don't want to
do so instead this writes to columns B,C & D in sheet 2.

This should work as worksheet code or in a module

Sub copyit()
lastrows1 = Sheets("Sheet1").Range("A65536").End(xlUp).Row
lastrows2 = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Dim MyRangeS1, MyRangeS2 As Range
Set MyRangeS1 = Sheets("Sheet1").Range("A1:A" & lastrows1)
Set MyRangeS2 = Sheets("Sheet2").Range("A1:A" & lastrows2)
For Each c1 In MyRangeS1
For Each c2 In MyRangeS2
If c1 = c2 Then
c2.Offset(0, 1).Value = c1.Offset(0, 13).Value
c2.Offset(0, 2).Value = c1.Offset(0, 14).Value
c2.Offset(0, 3).Value = c1.Offset(0, 15).Value
End If
Next
Next
End Sub

Mike




"thomsonpa" wrote:

How do I write the visual basic code for the following.
If the value in the cells in column A (sheet 1) matches the value in column
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

Many thanks, in anticipation.

your welcome and thanks for the feedback

Mike

"thomsonpa" wrote:

Thank you ever so much Mike, you were right about a and b. It is no wonder I
can never get my visual basic to work when I make such mistakes. Still, I am
learning all the time from this discussion group.

Your answer worked first time.

"Mike H" wrote:

Hi,

I didn't quite follow your logic

A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

This would overwrite the value in column A which I assume you don't want to
do so instead this writes to columns B,C & D in sheet 2.

This should work as worksheet code or in a module

Sub copyit()
lastrows1 = Sheets("Sheet1").Range("A65536").End(xlUp).Row
lastrows2 = Sheets("Sheet2").Range("A65536").End(xlUp).Row
Dim MyRangeS1, MyRangeS2 As Range
Set MyRangeS1 = Sheets("Sheet1").Range("A1:A" & lastrows1)
Set MyRangeS2 = Sheets("Sheet2").Range("A1:A" & lastrows2)
For Each c1 In MyRangeS1
For Each c2 In MyRangeS2
If c1 = c2 Then
c2.Offset(0, 1).Value = c1.Offset(0, 13).Value
c2.Offset(0, 2).Value = c1.Offset(0, 14).Value
c2.Offset(0, 3).Value = c1.Offset(0, 15).Value
End If
Next
Next
End Sub

Mike




"thomsonpa" wrote:

How do I write the visual basic code for the following.
If the value in the cells in column A (sheet 1) matches the value in column
A (sheet 2), copy contents of cells N, O & P, columns in the same row, to
sheet 2 in A,B & C.

Many thanks, in anticipation.