I have an Excel workbook with several worksheets.
I would like the macro to look at the value of cell "A1" of each worksheet.
If the cell value is less than 8, A1 must be adjusted to 8.
If the cell value is greater than 8, nothing needs to be adjusted.
I have two macros:
Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
Dim sh As Worksheet
For Each sh In Sheets
Select Case sh.Name
Case Is = "Blad1", "Blad2", "Blad3"
'No Code here if excluded
Case Else
Call X2
End Select
Next sh
End Sub
and
Sub X2()
'declare a variable
Dim ws As Worksheet
Set ws = ActiveSheet
'calculate if a cell is less than a specific value
If ws.Range("A1") < 8 Then
ws.Range("A1") = 8
Else
End If
End Sub
The problem is that only the active worksheet is done and the rest of the worksheets are not looked at. The macro also does not check whether the worksheet should not be included.
If you want using two subs, please try the next way. Your code only use the active sheet in the second sub:
Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
Dim sh As Worksheet
For Each sh In Sheets
Select Case sh.name
Case "Blad1", "Blad2", "Blad3"
'No Code here if excluded
Case Else
Call X2(sh)
End Select
Next sh
End Sub
Sub X2(ws As Worksheet)
'calculate if a cell is less than a specific value
If ws.Range("A1").value < 8 Then ws.Range("A1") = 8
End Sub
But for such a simple processing, no need of the second since everything can be done in the first one:
Sub LoopCertain() 'Excel VBA to exclude sheets(1-3)
Dim sh As Worksheet
For Each sh In Sheets
Select Case sh.name
Case "Blad1", "Blad2", "Blad3"
'No Code here if excluded
Case Else
If sh.Range("A1").value < 8 Then sh.Range("A1") = 8
End Select
Next sh
End Sub