Hello everyone,
In this forum, I found a macro to copy an entire row based on value and to paste in different sheets. First of all, let me say that I am not an expert in creating macros. I have made some modifications to the original one, added one more criteria.
Sub Copy()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("LIST").Cells(Rows.Count, "B").End(xlUp).Row
lr2 = Sheets("ABOVE 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
lr3 = Sheets("BELOW 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 2 Step -1
If Range("E" & r).Value = "17-18" And Range("D" & r).Value >50000 Then
Rows(r).Copy Destination:=Sheets("ABOVE 50000 17-18").Range("B" & lr2 + 1)
lr2 = Sheets("ABOVE 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
End If
If Range("E" & r).Value = "17-18" And Range("D" & r).Value <50000 Then
Rows(r).Copy Destination:=Sheets("BELOW 50000 17-18").Range("B" & lr2 + 1)
lr2 = Sheets("BELOW 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub
When the macro was run, it used to copy the entire row and paste it to different sheets based on the conditions. It was working fine, until I changed Column A to Column B. In addition to this, I have put a formula in column A in the destination sheets. When I run the macro, it says that the copy area and paste area are not of the same size and so can't paste it. I have tried my best to make it work, but it isn't.
Instead of copying the entire row, can this be modified to copy a range of cells in a row, when the conditions are met ?
Can anyone please help me out ?
LIST
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:41px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
ABOVE 50000 17-18
<tbody>
</tbody>
<tbody>
</tbody>
BELOW 50000 17-18
<tbody>
</tbody>
<tbody>
</tbody>
Thanks in advance.
In this forum, I found a macro to copy an entire row based on value and to paste in different sheets. First of all, let me say that I am not an expert in creating macros. I have made some modifications to the original one, added one more criteria.
Sub Copy()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("LIST").Cells(Rows.Count, "B").End(xlUp).Row
lr2 = Sheets("ABOVE 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
lr3 = Sheets("BELOW 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
For r = lr To 2 Step -1
If Range("E" & r).Value = "17-18" And Range("D" & r).Value >50000 Then
Rows(r).Copy Destination:=Sheets("ABOVE 50000 17-18").Range("B" & lr2 + 1)
lr2 = Sheets("ABOVE 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
End If
If Range("E" & r).Value = "17-18" And Range("D" & r).Value <50000 Then
Rows(r).Copy Destination:=Sheets("BELOW 50000 17-18").Range("B" & lr2 + 1)
lr2 = Sheets("BELOW 50000 17-18").Cells(Rows.Count, "B").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub
When the macro was run, it used to copy the entire row and paste it to different sheets based on the conditions. It was working fine, until I changed Column A to Column B. In addition to this, I have put a formula in column A in the destination sheets. When I run the macro, it says that the copy area and paste area are not of the same size and so can't paste it. I have tried my best to make it work, but it isn't.
Instead of copying the entire row, can this be modified to copy a range of cells in a row, when the conditions are met ?
Can anyone please help me out ?
LIST
A | B | C | D | E | |
1 | Number | Name | Amount | Year | |
2 | 1234 | ABCD | 51000 | 17-18 | |
3 | 2345 | BCDE | 45000 | 17-18 | |
4 | 3456 | CDEF | 1000 | 17-18 |
<colgroup><col style="font-weight:bold; width:30px; "><col style="width:41px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>
Excel tables to the web >> Excel Jeanie HTML 4
ABOVE 50000 17-18
A | B | C | D | E | F | |
1 | Sr.No | Number | Name | Amount | Year | |
2 | 1 | |||||
3 | 2 | |||||
4 | 3 |
<tbody>
</tbody>
Spreadsheet Formulas | ||||||||
<tbody> </tbody> |
<tbody>
</tbody>
BELOW 50000 17-18
A | B | C | D | E | F | |
1 | Sr.No | Number | Name | Amount | Year | |
2 | 1 | |||||
3 | 2 | |||||
4 | 3 |
<tbody>
</tbody>
Spreadsheet Formulas | ||||||||
<tbody> </tbody> |
<tbody>
</tbody>
Thanks in advance.