Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows if
I was trying to copy rows from Sheet2 to Sheet3 if Sheet2 A:A.value= Sheet1
B1.value |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows if
Hi Jakob, I'm not able to get any result..
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy rows if
Perfect, Jacob.. Thank you..
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Change a Macro - Copy in Columns instead of copy in Rows | Excel Programming | |||
Copy rows from one worksheet automatically, ignore rows that are b | Excel Worksheet Functions | |||
Copy pasting Rows, but need to Delete any Shapes/Pictures that are within copied rows | Excel Programming | |||
Copy rows of data (eliminating blank rows) from fixed layout | Excel Discussion (Misc queries) | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions |