lionginass
New Member
- Joined
- Jul 29, 2016
- Messages
- 24
Hello.
Have 2 sheets: "Data" and "Suppliers"
Trying to create a vba code, which scans Sheet"Data" column C and if there is value "No" in a cell, then copy columns A:B (same row where value "No" was found)
Then the code should paste that range in a sheet "Suppliers" Column B:C (last row)
Attaching photos of both sheets.
My point is to copy from sheet "Data" Range A1:B2 and paste it to sheet "Suppliers" Range B4:C5
My file is actually much bigger, but i made it simplier to test the code.
My code is here:
Sub Add()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Integer
Dim LR2 As Integer
Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Suppliers")
LR1 = ws1.UsedRange.Rows.Count
LR2 = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
For i = 1 To LR1
If ws1.Range("C" & i).Value = "No" Then
ws1.Range(Cells(i, 2), Cells(i, 3)).Copy ws2.Cells(LR2, 2)
LR2 = LR2 + 1
End If
Next i
Getting error : Method range of object Worksheet failed
Any ideas what is wrong?
Have 2 sheets: "Data" and "Suppliers"
Trying to create a vba code, which scans Sheet"Data" column C and if there is value "No" in a cell, then copy columns A:B (same row where value "No" was found)
Then the code should paste that range in a sheet "Suppliers" Column B:C (last row)
Attaching photos of both sheets.
My point is to copy from sheet "Data" Range A1:B2 and paste it to sheet "Suppliers" Range B4:C5
My file is actually much bigger, but i made it simplier to test the code.
My code is here:
Sub Add()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Integer
Dim LR2 As Integer
Set ws1 = Worksheets("Data")
Set ws2 = Worksheets("Suppliers")
LR1 = ws1.UsedRange.Rows.Count
LR2 = ws2.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
For i = 1 To LR1
If ws1.Range("C" & i).Value = "No" Then
ws1.Range(Cells(i, 2), Cells(i, 3)).Copy ws2.Cells(LR2, 2)
LR2 = LR2 + 1
End If
Next i
Getting error : Method range of object Worksheet failed
Any ideas what is wrong?