VBA Copy paste columns in different sheet

I have two sheets – sheet 1, sheet 2. I need to copy col M from sheet 1 and paste it into col D of sheet 2 only if sheet 1 col A has the string “COMPATIBLE” and col B has the string “Pass”. Example: Sheet 1:

A                                               B                                            M

COMPATIBLE                 Fail                                          1

NON COMPATIBLE   PASS                                       2

COMPATIBLE               PASS                                       3

COMPATIBLE               PASS                                       4

 

Now in sheet 2 “D” col the value of M should be pasted: i.e:

D

3

4

Top Contributor Asked on January 11, 2017 in VBA: General.
Add Comment
6 Answer(s)

I found the answer. Thanks 🙂

Dim lRow As Integer, i As Integer, j As Integer
Dim ws1, ws2 As Worksheet
Set ws1 = ThisWorkbook.Sheets("Latency")
Set ws2 = ThisWorkbook.Sheets("TP")
'Find last roe in Sheet1
lRow = ws1.Cells.SpecialCells(xlLastCell).Row
j = 2
For i = 1 To lRow
If ws1.Range("E" & i) = "COMPATIBLE" And ws1.Range("O" & i) = "Pass" Then
Top Contributor Answered 7 days ago.
Add Comment

HI,

Here is the solution for your query to copy data from sheet1(Column ‘M’) to sheet2(Column ‘D’).

Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
'Find last roe in Sheet1
lRow = Sheet1.Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If UCase(Sheet1.Range("A" & i)) = "COMPATIBLE" And UCase(Sheet1.Range("B" & i)) = "PASS" Then
Sheets("Sheet1").Range("M" & i).Copy Destination:=Sheets("Sheet2").Range("A" & j)
j = j + 1
End If
Next
End Sub

 

Thanks!

Expert Answered on January 11, 2017.
Add Comment

Here is link to find some useful information related to your query.

http://analysistabs.com/excel-vba/copy-data-from-one-worksheet-to-another/

Thanks!

Expert Answered on January 11, 2017.
Add Comment

Thank you so much! Just a small doubt, how do I use col name here to search here instead of col range?

Example, if ColA is named as Column A and ColB is named as Column B , how do I modify this to make sure the code searches for the particular text if the col name matches  Column A and Column B?

Top Contributor Answered on January 12, 2017.
Add Comment

Hi I tried the code and it was throwing up an error message  – “Object required”

Repleaced sheet1 with Latency and sheet2 with TP

This is the code I used:

Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
'Find last roe in Sheet1
lRow = Latency.Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If UCase(Latency.Range("E" & i)) = "COMPATIBLE" And UCase(Latency.Range("O" & i)) = "Pass" Then
Sheets("Latency").Range("M" & i).Copy Destination:=Sheets("TP").Range("A" & j)
j = j + 1
End If
Next
End Sub
Top Contributor Answered 7 days ago.
Add Comment

I found my error and fixed the code to this:

Sub sbMoveData()
Dim lRow As Integer, i As Integer, j As Integer
'Find last roe in Sheet1
With Worksheets("Latency")
lRow = .Cells.SpecialCells(xlLastCell).Row
j = 1
For i = 1 To lRow
If UCase(.Range("E" & i)) = "COMPATIBLE" And UCase(.Range("O" & i)) = "Pass" Then
.Range("M" & i).Copy Destination:=Worksheets("TP").Range("D" & j)
j = j + 1
End If
Next
End With
End Sub

 

But I”m not getting any result 🙁

Top Contributor Answered 7 days ago.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.