Copy rows if
I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1
B1.value |
Copy rows if
Dear Kashya
Try the below and feedback (untested) Sub MyMacro() Dim lngRow As Long Dim lngLastRow As Long Dim lngNewRow As Long Dim varTemp As Variant lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row lngNewRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 2 To lngLastRow If Sheets("Sheet1").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") Then varTemp = Sheets("Sheet1").Range(lngRow & ":" & lngRow) Sheets(2).Range(lngRow & ":" & lngRow) = varTemp lngNewRow = lngNewRow + 1 End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1 B1.value |
Copy rows if
Modified to suit your requirement Sheet2 to Sheet3 referring Sheet1 B1....
Sub MyMacro() Dim lngRow As Long Dim lngLastRow As Long Dim lngNewRow As Long Dim varTemp As Variant lngLastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row lngNewRow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 1 To lngLastRow If Sheets("Sheet2").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") Then varTemp = Sheets("Sheet2").Range(lngRow & ":" & lngRow) Sheets(3).Range(lngNewRow & ":" & lngNewRow) = varTemp lngNewRow = lngNewRow + 1 End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Dear Kashya Try the below and feedback (untested) Sub MyMacro() Dim lngRow As Long Dim lngLastRow As Long Dim lngNewRow As Long Dim varTemp As Variant lngLastRow = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row lngNewRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row For lngRow = 2 To lngLastRow If Sheets("Sheet1").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") Then varTemp = Sheets("Sheet1").Range(lngRow & ":" & lngRow) Sheets(2).Range(lngRow & ":" & lngRow) = varTemp lngNewRow = lngNewRow + 1 End If Next End Sub -- If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1 B1.value |
Copy rows if
Hi Jakob, I'm not able to get any result..
|
Copy rows if
I have tried with the below data in a new workbook. Since I havent refered
the workbook name it works with the active workbook. Try the below Sheet1 B1 = 1 Sheet2 Col A and Col B with below data 1 a 2 b 1 a 2 b 1 a and Sheet3 blank If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Hi Jakob, I'm not able to get any result.. |
Copy rows if
Sorry Jocob, there was a slight error while editing.. Its working fine now..
:) can we also copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1 B1.value & Sheet2 A:A.value<= Sheet1 B2.value ? |
Copy rows if
Hi Kashya
Try and feedback.. Sub MyMacro() Dim lngRow As Long Dim lngLastRow As Long Dim lngNewRow As Long Dim varTemp As Variant lngLastRow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row lngNewRow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row + 1 For lngRow = 1 To lngLastRow If Sheets("Sheet2").Range("A" & lngRow) = Sheets("Sheet1").Range("B1") And _ Sheets("Sheet2").Range("A" & lngRow) <= Sheets("Sheet1").Range("B2") Then varTemp = Sheets("Sheet2").Range(lngRow & ":" & lngRow) Sheets(3).Range(lngNewRow & ":" & lngNewRow) = varTemp lngNewRow = lngNewRow + 1 End If Next End Sub If this post helps click Yes --------------- Jacob Skaria "Kashyap" wrote: Sorry Jocob, there was a slight error while editing.. Its working fine now.. :) can we also copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1 B1.value & Sheet2 A:A.value<= Sheet1 B2.value ? |
Copy rows if
Perfect, Jacob.. Thank you..
|
All times are GMT +1. The time now is 11:50 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com