Hi Experts,
I attached the sample file with input and output sheets
I tried the below code to copy the data from input-item sheet and paste it in the respected output sheets if certain condition meets
and it is working fine but while i am trying to delete the rows which i copied from input-item the below code is not working fine it only deletes only some of the rows from input and not the all.
kindly help me to delete the copied rows
Sub macro()
Dim ws As Worksheet
Dim lrow As Long, lcol As Long, i As Long, j As Long, x As Long, y As Long, lastrow As Long
Application.ScreenUpdating = False
x = 2
Set ws = Worksheets("Output - Ignored")
Set ft = Worksheets("Output - Comments")
ws.Range("A1:G1").Value = Split("ITEM_CODE,ITEM_NAME,UNIT_NAME,PRICE,ITEM_ID,ignored,Comments", ",")
With Worksheets("Input - item")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To lrow
For j = 6 To 6
If .Cells(i, j).Value = "IGNORE" Or .Cells(i, j).Value = "Ignore" Or .Cells(i, j).Value = "ignore" Or .Cells(i, j).Value = "-" Then
.Cells(i, j).EntireRow.Copy
ws.Cells(x, 1).EntireRow.PasteSpecial
x = x + 1
End If
Next j
Next i
For i = 2 To lrow
For j = 6 To 6
If .Cells(i, j).Value = "IGNORE" Or .Cells(i, j).Value = "Ignore" Or .Cells(i, j).Value = "ignore" Or .Cells(i, j).Value = "-" Then
.Cells(i, j).EntireRow.Delete
End If
Next j
Next i
ft.Range("A1:G1").Value = Split("ITEM_CODE,ITEM_NAME,UNIT_NAME,PRICE,ITEM_ID,ignored,Comments", ",")
x = 2
For i = 1 To lrow
For j = 7 To 7
If .Cells(i, j).Value = "future test" Then
.Cells(i, j).EntireRow.Copy
ft.Cells(x, 1).EntireRow.PasteSpecial
x = x + 1
End If
Next j
Next i
For i = 1 To lrow
For j = 7 To 7
If .Cells(i, j).Value = "future test" Then
.Cells(i, j).EntireRow.Delete
End If
Next j
Next i
End With
MsgBox "Done"
Application.ScreenUpdating = True
End Sub
Thank you in advance for your help.
Bookmarks