Hi, So this was supposed to be fairly straight forward.
I have two lists in two sheets in one excel file. (Sheet 1 and Sheet 2 in Workbook A)
Then I have two other excel files with 1 sheet in each of them. Sheet 1 Work Book B. Sheet 1 Work Book C.
So Sheet 1 in Workbook "Main" has a list of values in column A. I need a vba script that can look through column A. and find the matching value in Column A of Sheet 1 workbook "Rad" and IF Found then delete the row in Sheet 1 WorkBook "Rad".
Then I need it to look Column A in Sheet 2 of work Book "Main" and find the matching value in column A Sheet 1 Work Book "Vogue". And if it finds the value then delete the row in Workbook "Vogue".
Here is the code so far.
I don't know why but I get "Cells' of Object'_Worksheet' failed - error message. Can someone please tell me what I am doing wrong?
I have two lists in two sheets in one excel file. (Sheet 1 and Sheet 2 in Workbook A)
Then I have two other excel files with 1 sheet in each of them. Sheet 1 Work Book B. Sheet 1 Work Book C.
So Sheet 1 in Workbook "Main" has a list of values in column A. I need a vba script that can look through column A. and find the matching value in Column A of Sheet 1 workbook "Rad" and IF Found then delete the row in Sheet 1 WorkBook "Rad".
Then I need it to look Column A in Sheet 2 of work Book "Main" and find the matching value in column A Sheet 1 Work Book "Vogue". And if it finds the value then delete the row in Workbook "Vogue".
Here is the code so far.
VBA Code:
Dim U1 As Worksheet, U2 As Workbook, U3 As Worksheet, i As Long, fn As Range, L1 As Worksheet, L2 As Workbook, L3 As Worksheet, l As Long, ln As Range
Set U1 = Workbooks("Main.xlsm").Sheets("Sheet 1")
Set U2 = Workbooks.Open("M:\Thisplace\Rad.csv")
Set U3 = U2.Sheets(1)
For i = U3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Set fn = U1.Columns("A").Find(U3.Cells(i, 1).Value)
If Not fn Is Nothing Then Rows(i).Delete
U2.Close Savechanges:=True
Next
Set L1 = Workbooks("Main.xlsm").Sheets("Sheet 2")
Set L2 = Workbooks.Open("M:\Thisplace\Vogue.csv")
Set L3 = L2.Sheets(1)
For l = L3.Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
Set ln = L1.Columns("A").Find(L3.Cells(i, 1).Value)
If Not ln Is Nothing Then Rows(i).Delete
L2.Close Savechanges:=True
Next
ThisWorkbook.Close Savechanges:=True
End Sub
I don't know why but I get "Cells' of Object'_Worksheet' failed - error message. Can someone please tell me what I am doing wrong?
Last edited by a moderator: