Macro to find a value in excel

Hello there,

My code is not working. Can anyone guide me on how to correct it?

I would like to find if the value “2021” exists in any cell of any sheet, including checking through all hidden tabs.
One sheet may even have this value appearing for a few times at different cells.
If the value is found, I would like to write the cell address in a new sheet.

Sub FindOutdatedFYCells()
Dim ws As Worksheet
Dim destSheet As Worksheet
Dim cell As Range
Dim lastRow As Long

Set destSheet = 
ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
destSheet.Name = "Outdated FY"
destSheet.Range("A1").Value = "Sheet Name"
destSheet.Range("B1").Value = "Cell Address"

For Each ws In ThisWorkbook.Worksheets
    For Each cell In ws.UsedRange.Cells
        If InStr(cell.Value,"2021")>0 Then
            lastRow = destSheet.Cells(destSheet.Rows.Count, 1).End(xlUp).Row + 1
            destSheet.Cells(lastRow, 1).Value = ws.Name
            destSheet.Cells(lastRow, 2).Value = cell.Address
        End If
    Next cell
Next ws

End Sub

Hi @WS_Chai ,
You can try my step
-read all sheet and check data table return index
Regards,
LNV

Hi Nguyen,

Thanks for helping. Do you min to explain your approach in detailed?

I am not sure what range to put since each sheet has a unique name.



Hi @WS_Chai ,
You can get sheet name by activity by index
Regards,
LNV

@Nguyen_Van_Luong1

Sorry what does that mean?


get worksheet by index