I can’t seem to find any decent documentation on this stuff, so I usually have to guess and experiment….
Application Evaluate seems to work on the active workbook
Worksheets(“fhkafg”) Evaluate seems to works on the worksheet, in this case with name
“fhkafg”
At the same time, you can reference any other worksheet using the
ISREF in a worksheet.
Try this… put the test routine ,
Sub TestWsEvaluate(), in any code module in a workbook.
Activate
any other workbook.
Run
Sub TestWsEvaluate(),
It seems to work…
Code: Select all
Sub TestWsEvaluate() ' https://www.eileenslounge.com/viewtopic.php?f=30&t=31883&p=247031#p247035
Dim WsInLisWb As Worksheet
For Each WsInLisWb In ThisWorkbook.Worksheets
Dim IsIt As Boolean
Let IsIt = WsInLisWb.Evaluate("ISREF('" & "Sht_1" & "'!G6)"): Debug.Print IsIt
Next WsInLisWb
End Sub
So you could do what you want to do using
Worksheets(“___”) Evaluate
You can use any worksheet in the workbook where the routine is. The workbook with the code in does not need to be active when using
Worksheets(“___”) Evaluate
Code: Select all
Sub Test3() ' Use Worksheets(" ") Evaluate ' https://www.eileenslounge.com/viewtopic.php?f=30&t=31883&p=247031#p247035
Dim wb As Workbook, ws As Worksheet
Dim myPath As String, myFile As String
Let myPath = ThisWorkbook.Path & "\"
Let myFile = Dir(myPath & "*.xls*")
Do While myFile <> ""
If UCase(myFile) <> UCase(ThisWorkbook.Name) Then
Set wb = Workbooks.Open(myPath & myFile, False)
For Each ws In wb.Worksheets
If ThisWorkbook.Worksheets.Item(1).Evaluate("ISREF('" & ws.Name & "'!Hi42)") = False Then
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
End If
Next ws
wb.Close SaveChanges:=True
End If
myFile = Dir
Loop
End Sub
I am using
Item(1) because you will have at least that first tab worksheet.
But, you can use any worksheet that is in your main file, that is to say, the file in which that code above is in… but that File need not be active for
Sub Test3() to work
( Usually when people say "Evaluate" they are talking about Application Evaluate. As we have seen "Evaluate" don't work for what you want to do with it ..... Worksheets(" ") Evaluate is something else...... )
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
