【VBA】CountIfで別シートを参照【Worksheetsを使う】

'WorksheetFunctionで別シートを参照
With Worksheets("Sheet2")
    Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
End With
    
'数式埋め込みで別シートを参照
Worksheets("Sheet1").Cells(2, "A") = "=COUNTIF(Sheet2!A:A,""B"")"

Sub TEST1()
    
    '別シートの合計値を算出
    Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(Worksheets("Sheet2").Range("A:A"), "B")
    
End Sub

Sub TEST2()
    
    '別シートの合計値を算出
    With Worksheets("Sheet2")
        Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
    End With
    
End Sub

Sub TEST3()
    
    'シート番号を使って、別シートの合計値を算出
    With Worksheets(2)
        Worksheets("Sheet1").Cells(2, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
    End With
    
End Sub

Sub TEST4()
    
    'シートをループ
    For i = 2 To 5
        '別シートの合計値を算出
        With Worksheets(i)
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
        End With
    Next
    
End Sub

Sub TEST5()
    
    '最終シートまでループ
    For i = 2 To Worksheets.Count
        '別シートの合計値を算出
        With Worksheets(i)
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.CountIf(.Range("A:A"), "B")
        End With
    Next
    
End Sub

Sub TEST6()
    
    i = 1
    'すべてのシートをループ
    For Each a In Worksheets
        'Sheet1のシートではないとき
        If a.Name <> "Sheet1" Then
            i = i + 1 'カウントアップ
            '別シートの合計値を算出
            Worksheets("Sheet1").Cells(i, "A") = WorksheetFunction.CountIf(a.Range("A:A"), "B")
        End If
    Next
    
End Sub

Sub TEST7()
    
    '別シートの合計値を算出
    Worksheets("Sheet1").Cells(2, "A") = "=COUNTIF(Sheet2!A:A,""B"")"
    Worksheets("Sheet1").Cells(2, "A").Value = Worksheets("Sheet1").Cells(2, "A").Value '値に変換
    
End Sub

Sub TEST8()
    
    'シートをループ
    For i = 2 To 5
        a = Worksheets(i).Name 'シートの名前
        '別シートの合計値を算出
        Worksheets("Sheet1").Cells(i, "A") = "=COUNTIF(" & a & "!A:A,""B"")"
    Next
    
    '値に変換
    Worksheets("Sheet1").Range("A2:A5").Value = Worksheets("Sheet1").Range("A2:A5").Value
    
End Sub

Sub TEST9()
    
    '最終シートまでループ
    For i = 2 To Worksheets.Count
        a = Worksheets(i).Name
        '別シートの合計値を算出
        Worksheets("Sheet1").Cells(i, "A") = "=COUNTIF(" & a & "!A:A,""B"")"
    Next
    
    '値に変換
    Worksheets("Sheet1").Range("A2:A" & Worksheets.Count).Value = Worksheets("Sheet1").Range("A2:A" & Worksheets.Count).Value
    
End Sub

Sub TEST10()
    
    i = 1
    '最終シートまでループ
    For Each a In Worksheets
        'Sheet1ではないとき
        If a.Name <> "Sheet1" Then
            i = i + 1 'カウントアップ
            '別シートの合計値を算出
            Worksheets("Sheet1").Cells(i, "A") = "=COUNTIF(" & a.Name & "!A:A,""B"")"
        End If
    Next
    
    '値に変換
    Worksheets("Sheet1").Range("A2:A" & i).Value = Worksheets("Sheet1").Range("A2:A" & i).Value
    
End Sub