Activate all sheets without flickering through them-CodePudding

I have over 20 sheets with VBA codes that performs calculations realtime and simultaneously. All the calculations on each sheet are working fine except some COUNTIF and FIND ADDRESS function whereby VBA ignores running them on every other sheet unless I'm active on that sheet, then it works.

I have tried several methods and this one works by activating all the sheets from another sub

Worksheets("Sheet2").activate
Worksheets("Sheet3").activate
Worksheets("Sheet4").activate

By doing this, the COUNTIF and FIND ADDRESS functions works on all sheets however, it's flickering through all of the sheets. I was also able to get it to stop on one sheet by adding (Worksheets("Sheet1").activate) at the end of the last sub. This doesn't fix the issue as I am unable to check any other sheet. I also tried

Application.ScreenUpdating = False 'At the beginning of the sub
Application.ScreenUpdating = True 'At the end of the sub

No luck. Tried wrapping each code in the vba around

Dim ws As Worksheets
ws.activate

Doesn't fix the issue. How can I activate all sheets without flickering through them? If activating them all at once can't fix the issue, is there another way? Thank you

Here is the sample of the code -

psup = "Generated" & " " & lBar

If Abs(sp2) = 0 Then
If Cells.Find(psup).Offset(-8, 0).Value > 3 Or Cells(b   1, h).Offset(-8, 0).Value > 3 Then
Call allNewYes
'Cells(b - 7, h).Value = Cells(b - 7, h).Value   4
sp2 = 1
End If
End If




'1.Get Position - Generated
If Application.WorksheetFunction.CountIf(ActiveSheet.Cells, psup) > 2 Then
sp6 = Application.WorksheetFunction.CountIf(ActiveSheet.Cells, psup) - 1
Call spLocation
Else
If Application.WorksheetFunction.CountIf(ActiveSheet.Cells, psup) > 0     Then
sp5 = Cells.Find(psup).Address
End If
End If




Sub allNewYes()
Dim locazion As String
Dim FindValue As String
FindValue = psup

Dim FindRng As Range
Set FindRng = Cells.Find(What:=FindValue)

Dim FirstCell As String
FirstCell = FindRng.Address

Do
 locazion = FindRng.Address
 Range(locazion).Offset(-8, 0).Value = Abs(Range(locazion).Offset(-8, 0).Value)   4
 Set FindRng = Cells.FindNext(FindRng)
 Loop While FirstCell <> FindRng.Address
End Sub

CodePudding user response：

Here is a mock-up of how you perform actions on multiple worksheets, without selecting or activating them - using part of your code as an example. I wasn't sure how you create FindValue - so you'd have to do that part yourself.

Sub perform_actions_on_all_sheets()

    Dim wb As Workbook, ws As Worksheet, FindRng As Range, FirstCell As String
    
    FindValue = 5 'change this to something appropriate

    Set wb = ThisWorkbook
    
    For Each ws In wb.Sheets
    
        If ws.Name <> "ExcludeThisWorksheetName" Then
        
            'do stuff to ws, e.g.
            
            Set FindRng = ws.Cells.Find(What:=FindValue)
            
            If Not (FindRng Is Nothing) Then
                FirstCell = FindRng.Address
                
                Do
                    FindRng.Offset(-8, 0).Value = Abs(FindRng.Offset(-8, 0).Value)   4
                    Set FindRng = ws.Cells.Find(FindValue, LookIn:=xlValues, LookAt:=xlWhole)
                
                Loop While FirstCell <> FindRng.Address
            End If
        End If
    
    Next

End Sub

The If ws.Name <> "ExcludeThisWorksheetName" Then ... End If is optional - this is usually required if you want to run the script on every tab except one.

CodePudding user response：

For anyone experiencing similar issues, by removing (ActiveSheet.Cells) in my code fixed the issue