VLOOKUP with multiple criteria with mutiple sheets

[narsing18]

i am looking for a vlookup VBA code for my project.


i have Multiple sheets like sheet1,sheet2,sheet3,....upto sheet6 in these sheets i have different columns and a usernames column with different usernames. i have one more sheet7 in that i have only one column with fixed usernames


now i want to vlookup sheet1 to sheet6 with sheet7 and what ever names are not matching with usernames in sheet7(i.e NA) should be deleted in all sheets from sheet1 to sheet6 and which are maching should be displayed


Please let me know if its possible with VBA code

 

[gallen]

could you not use VBA to just tidy up the usernames ie Try to find each name on sheet7 in sheets 1- 6. If not found delete the row>?

 

[gallen]

This code will delete any names not found. Need to adjust for the column your names are in on sheet 7 and check the first row number is correct.

Any Item you may need to change is highlighted in red

Sub DeleteNotFound()
    
    Dim ws As Worksheet
    
    Dim lLast As Long 'last row of data
    Dim lFirst As Long 'first row of data
    Dim l As Long 'counter
    
    Dim rFind As Range
    Dim sToFind As String
    
    lFirst = [B][COLOR=#ff0000]2[/COLOR][/B] 'First Row of data. Change as required
    lLast = Range("A" & Rows.Count).End(xlUp).Row
    
    For l = lLast To lFirst Step -1 'count backwards as we are deleting
    
        sToFind = Range("[COLOR=#ff0000][B]A[/B][/COLOR]" & l)
        
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "[B][COLOR=#ff0000]Sheet7[/COLOR][/B]" Then 'Don't search sheet7
                Set rFind = ws.Cells.Find(sToFind)
                If Not rFind Is Nothing Then 'Name found so keep it
                    GoTo NextName 'don't search any more as it is found
                End If
            End If
        Next ws
        Rows(l).Delete 'we will only get here if name isn't found
NextName:
    Next l
    
End Sub

 

[narsing18]

could you not use VBA to just tidy up the usernames ie Try to find each name on sheet7 in sheets 1- 6. If not found delete the row>?

i am trying to prepare a report in that its a first step to Vlookup all sheets1-6 with sheet 7 and what are names matches with sheet 7 that are to be carried to next step in the report. and rows count may change daily (i.e usernames will keep changing daily in sheet 1-6 )

 

[gallen]

Sorry I've just re-read your OP My code does the opposite of what you ask so Ignore it.

 

[gallen]

i want to vlookup sheet1 to sheet6 with sheet7 and what ever names are not matching with usernames in sheet7(i.e NA) should be deleted in all sheets from sheet1 to sheet6 and which are maching should be displayed

So in essence,if any name on sheets 1 - 6 isn't found on sheet7 then delete it.

You will need to provide the columns on each sheet that contain the user names to find in sheet 7

 

[narsing18]

So in essence,if any name on sheets 1 - 6 isn't found on sheet7 then delete it.

You will need to provide the columns on each sheet that contain the user names to find in sheet 7

its looks like below picture


in this sheet1-6 are data sheets ( with lots of data and many columns) and sheet8 consists of username column only i.e A column(only one column)

in all sheets 1-6 username column is "B"

yes i need matching name should be exist in all 1-6 sheets other #NA name should be deleted.

 

[narsing18]

it looks something like above

column name is "USERNAMES"