But unable to track the issue
so i posted the question here
https://www.excelforum.com/excel-progra ... tched.html
Code: Select all
Sub test()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\1.xls")
Set wb2 = Workbooks.Open("C:\Users\**I've been banned**\Desktop\2.xlsx")
Set sh1 = wb1.Worksheets.Item(1)
Set sh2 = wb2.Worksheets.Item(2) '---> you put item(1), while the sheet in wb2 which has data is in sheet2.
Set rngWb1 = sh1.Range("B2", sh1.Range("B" & Rows.Count).End(xlUp))
Set rngWb2 = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
sh1.Cells(1, 10).Value = "KEEP"
With sh1
For Each c1 In rngWb2
sym = c1.Value
num = c1.Offset(0, 1).Value
For Each c2 In rngWb1
tt = c2.Value
If c2.Value = sym And c2.Offset(0, 7).Value = num Then _
c2.Offset(0, 8).Value = "keep"
Next
Next
.Range("A1").AutoFilter Field:=10, Criteria1:="="
.Range("A2", .Range("i" & Rows.Count).End(xlUp)) _
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.Cells.AutoFilter
End With
sh1.Cells(1, 10).EntireColumn.Delete
'wb1.Save
'wb1.Close
'wb2.Close
End Sub
Error Details