search a specific cell in multiple tabs and output the name of the tab

[chr4and]

i want to search within multiple tabs, the value of cell B1, and output the names of the tabs that include a specific value. For example, if tabs k2 and k4 contain the value 1, in cell B1, I want an output as a new sheet, that contains the names k2 and k4.

 

[chr4and]

Pls check this

Sub search_answer()

    Dim ws As Worksheet
    Dim current_ws As Worksheet
    Dim ans As String
    Dim last_row As Long
    Dim i As Long
    Dim j As Long
            
    Set current_ws = ThisWorkbook.Sheets("Answers")' Please change appropriately
   
    ans = "Ans1" 'please change appropriately
         
    lastrow = current_ws.Cells(current_ws.Rows.Count, "A").End(xlUp).Row
   
    lastQ = 10 'change as needed
   
    If Not Evaluate("ISREF('" & ans & "'!A1)") Then Sheets.Add.Name = ans 'checks whether sheet is already there
    'If Evaluate("ISREF('" & ans & "'!A1)") Then ThisWorkbook.Sheets(ans).delete
   
    Set ws = ThisWorkbook.Sheets("Ans1")
   
    'ws.Range("a1").Value = ws.Name
    'MsgBox "The sheet name is: " & ws.Name
    'ws.Range("A1:M20").ClearContents
   
    Start_row = "1"
    Start_col = "A"
    End_row = lastrow
    End_col = lastQ
   
    start_cell = Start_col & Start_row
    end_cell = ws.Cells(End_row, End_col).Address(RowAbsolute:=False, ColumnAbsolute:=False)
   
   
    ws.Range(start_cell & ":" & end_cell).Value = current_ws.Range(start_cell & ":" & end_cell).Value
           
    For j = 2 To lastQ + 1
        For i = 2 To lastrow
       
           If current_ws.Cells(i, j).Value = ans Then
                ws.Cells(i, j) = current_ws.Cells(i, j)
                ws.Range("A1").Name = current_ws.Name
            Else
                ws.Cells(i, j) = 0
            End If
        Next i
    Next j
End Sub

Extremely helpfull. Many thanks!!!

 

[arun_eagertolearn]

Thanks for letting know that it could be of help