how to keep worksheet.visible = false and still get data like activesheet name and cell address

Question

I have a list of excel file names and those file's path in 2 columns. Using the code , I open each worksheet at a time , search for a specific keyword in opened workbook , wherever match is found. I have to get that workbook name (already have) , matched found cell address , that active sheet name and value in cell.

Query 1: Now , already have code with me it works properly but when I hide the excel sheets so that user can't see them , the ActiveSheet can't be used code does not go to opened workbook instead it search in macro file , In simple words if I hide files using .visible = false it does not work , it search values in macro. Please help to fix it

Query 2: I think the code to paste value in macro is not 100% accurate , because I have just mentioned sheets not workbook , please help to make it solid

Query 3: How to make it faster if I want to search 10k files

I have tried the below code

  Dim lastvalueoffiles As Integer  


  lastvalueoffiles = Sheet4.Range("A" & Rows.Count).End(xlUp).Row


For EachFileinPath = 2 To lastvalueoffiles

    Dim wb As Workbook
    'wb.Visible = False

    Set wb = Workbooks.Open(Sheet4.Cells(EachFileinPath, 2).Value)
   ActiveWindow.Visible = True
    
          Dim SearchString As String
          Dim SearchRange As Range, cl As Range
          Dim FirstFound As String
          Dim sh As Worksheet
 
     '''Set Search value
     SearchString = Sheet1.Cells(6, 8).Value
    
           Application.FindFormat.clear
    
           '''loop through all sheets
    
    For Each sh In ActiveWorkbook.Worksheets
        
        ''' Find first instance on sheet
        Set cl = sh.Cells.Find(What:=SearchString, After:=sh.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
        sh.Activate
        
        If Not cl Is Nothing Then
            ' if found, remember location
            FirstFound = cl.Address
            ' format found cell
            
            Do
                        
               LastValueoftheresultfound = Sheet4.Cells(EachFileinPath, ActiveSheet.Columns.Count).End(xlToLeft).Column
                   
                momo = ActiveCell.Address
                
                ActiveSheet.Select
                
                
                Sheet4.Cells(EachFileinPath, LastValueoftheresultfound + 1).Value = ActiveSheet.Name
                
                Sheet4.Cells(EachFileinPath, LastValueoftheresultfound + 2).Value = cl.Address
                
                Sheet4.Cells(EachFileinPath, LastValueoftheresultfound + 3).Value = cl.Value
                
                '''cl.Interior.ColorIndex = 3
                
                Set cl = sh.Cells.FindNext(After:=cl)
                ' repeat until back where we started
            Loop Until FirstFound = cl.Address
        End If
    Next

ActiveWorkbook.Close SaveChanges:=False

koko = Sheet1.Cells(6, 8).Value
    
Next EachFileinPath

You don't need to select/activate the sheets or the cells. I recommend reading [How to avoid using select](https://stackoverflow.com/a/23913882/16578424). — Ike, May 17 '23 at 11:54
Sheets with Visible=False property cannot be the active sheet. You can reference them with their names, as an item in the Sheets or WorkSheets collection, with an assigned object variable. Even if Activate method not throw an error the invisible sheet will not be active. Therefore cannot be referenced as ActiveSheet. — Black cat, May 18 '23 at 10:35

score 1 · Answer 1 · answered May 17 '23 at 13:48

If I understand you correctly....

Before running the macro, sheet4 look something like this:

test1.xlsx sheet1 cell H6 value is aaa
test1.xlsx sheet2 cell A1 value is aaa
test1.xlsx sheet3 cell B1:B2 value is aaa
test1.xlsx sheet4 cell C1:C3 value is aaa

test2.xlsx, the cells are the same, but value is bbb
test3.xlsx, the cells are the same, but value is ccc.

Expected result (please ignore the fill color)

Sub test()
Dim shTrg As Worksheet, rg As Range, FL As String, OpenedWB As Workbook
Dim SearchString As String, sh As Worksheet, cl As Range

Application.ScreenUpdating = False

Set shTrg = ThisWorkbook.Sheets("Sheet4") 'change if needed
Set rg = shTrg.Range("B2", shTrg.Range("B" & Rows.Count).End(xlUp)) 'change if needed
Application.FindFormat.Clear

For Each FL In rg
    Set OpenedWB = Workbooks.Open(FL)
    SearchString = OpenedWB.Sheets("Sheet1").Cells(6, 8).Value
    For Each sh In OpenedWB.Worksheets
        Set cl = sh.Cells.Find(What:=SearchString, After:=sh.Cells(1, 1), _
            LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        If Not cl Is Nothing Then
            FirstFoundAddress = cl.Address
            Do
                With shTrg.Cells(FL.Row, shTrg.Columns.Count).End(xlToLeft).Offset(0, 1)
                .Resize(1, 3).Value = Array(sh.Name, cl.Address, cl.Value)
                End With
                Set cl = sh.Cells.FindNext(cl)
            Loop Until FirstFoundAddress = cl.Address
        End If
    Next sh
    OpenedWB.Close SaveChanges:=False
Next FL

Application.ScreenUpdating = False
End Sub

Please note that the sub may throw an error if the count of the found cells in the OpenedWorkbook exceed than the last column of the shTrg.

how to keep worksheet.visible = false and still get data like activesheet name and cell address

1 Answers1