Condition:If column H of 1.xls is greater than column D of 1.xls then copy third row of orderformat.xlsx & paste it to basketorder.xlsxCode:Sub STEP6() Dim Wb1 As Workbook Dim Wb2 As Workbook Dim wb3 As Workbook Dim Ws1 As Worksheet Dim Ws2 As Worksheet Dim ws3 As Worksheet Dim strPath As String Dim r As Long Dim m As Long Dim rng As Range Dim n As Long Application.ScreenUpdating = False Set Wb1 = Workbooks.Open(ThisWorkbook.Path & "1.xls") Set Ws1 = Wb1.Worksheets(1) m = Ws1.Range("H" & Ws1.Rows.Count).End(xlUp).Row strPath = ThisWorkbook.Path & "" Set Wb2 = Workbooks.Open(strPath & "OrderFormat.xlsx") Set Ws2 = Wb2.Worksheets(1) Ws2.Range("A1:A4").TextToColumns DataType:=xlDelimited, Tab:=True, _ SemiColon:=False, Comma:=False, Space:=False, Other:=False, _ ConsecutiveDelimiter:=False Set wb3 = Workbooks.Open(strPath & "BasketOrder..csv") Set ws3 = wb3.Worksheets(1) Set rng = ws3.Cells.Find(what:="*", searchorder:=xlByRows, SearchDirection:=xlPrevious) If rng Is Nothing Then n = 1 Else n = rng.Row + 1 End If For r = 2 To m If Ws1.Range("H" & r).Value > Ws1.Range("D" & r).Value Then Ws2.Range("A1").EntireRow.Copy Destination:=ws3.Range("A" & n) n = n + 1 ElseIf Ws1.Range("H" & r).Value < Ws1.Range("D" & r).Value Then Ws2.Range("A3").EntireRow.Copy Destination:=ws3.Range("A" & n) n = n + 1 End If Next r Application.DisplayAlerts = False Wb1.Close SaveChanges:=False Wb2.Close SaveChanges:=False wb3.SaveAs FileName:=strPath & "BasketOrder..csv", FileFormat:=xlCSV wb3.Close SaveChanges:=False Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub
If column H of 1.xls is smaller than column D of 1.xls then copy first row of orderformat.xlsx & paste it to basketorder.xlsx
sample file attached below




Reply With Quote

Bookmarks