how to vlookup Multiple sheets and delete set of ID's from all sheets

[narsing rao]

Hi,
i have 12 sheets in workbook in that sheet8 is the sheet where all the sheets vlookup with, in sheet8 i have only one column "A" with list of Customer name.

now i want what all 11 sheets should vlookup with sheet8 and what ever the names matches with sheet8 should be there which are not matching should be deleted.

i these i kept 3 sheets empty for later calculation

Sub VLdelete()

Dim arr(), msg AsStringDim ws_lrow, ws8_lrow, i AsIntegerDim ws As Worksheet

ws8_lrow = Sheets("Sheet8").Cells(Rows.Count,1).End(xlUp).Row

ReDim arr(ws8_lrow)

For i =2To ws8_lrow
arr(i -2)= Sheets("Sheet8").Cells(i,1).value
Next i

ForEach ws In ActiveWorkbook.Sheets
ws_lrow = ws.Cells(Rows.Count,2).End(xlUp).Row

For i = ws_lrow To2Step-1If IsInArray(ws.Cells(i,2), arr())=0Then
msg = msg &"User """& ws.Cells(i,2)&""" from: "& ws.Name & vbCrLf
ws.Cells(i,2).EntireRow.Delete xlShiftUp
EndIfNext iNext ws

If Len(msg)>0Then
MsgBox "The following users have been deleted:"& vbCrLf & msg
EndIf

EndSub

PrivateFunction IsInArray(valToBeFound AsVariant, arr AsVariant)AsBoolean

Dim element AsVariantOnErrorGoTo IsInArrayError:'array is emptyForEach element In arrIf element = valToBeFound Then
IsInArray =TrueExitFunctionEndIfNext element
ExitFunction

IsInArrayError:OnErrorGoTo0
IsInArray =False

EndFunction

this code was previously working fine but now giving error

but i am getting delete method of range class failed error ...at line no 24 at

ws.Cells(i,2).EntireRow.Delete xlShiftUp

please help

 

[Monty]

Hello narsing rao

Please upload file for quick solution.

Monty!