エクセルで単語がたくさんあるシートがあるとします（同じ単語が重複することがある）

動的配列をうまく活用すれば、できるとは思いますが……どうでしょう。

質問者さんの書かれている内容だけを見ると、いまいち状況がわかりませんが、憶測で書いてみました。

Option Explicit

Type RankingData    'ランキング用構造体
    Items As String 'ランキングのアイテム
    Values As Long  'アイテムの個数
End Type

Sub test()

    Dim V As Variant 'セル範囲のデータ取得用
    Dim R As Variant 'ForEachNext 用カウンタ
    Dim L As Long    '汎用データ用
    Dim C As Long    '汎用カウンタ
    Dim Ranking() As RankingData
    
    V = Worksheets("Sheet1").UsedRange      '使用されているセル範囲を取得
    ReDim Ranking(0)
    Ranking(0).Items = V(1, 1)
    
    For Each R In V
        L = UBound(Ranking)
        For C = 0 To L
            If R = Ranking(C).Items Then    '既出アイテムかどうかチェック
                Ranking(C).Values = Ranking(C).Values + 1
                Exit For
            Else
                If C = L Then
                    ReDim Preserve Ranking(L + 1)
                    Ranking(L + 1).Items = R
                    Ranking(L + 1).Values = 1
                    Exit For
                End If
            End If
        Next
    Next
    L = UBound(Ranking)
    With Worksheets("Sheet2")               'Sheet2 に集計した情報を吐き出す
        For C = 0 To L
            .Cells(C + 1, 1).Value = Ranking(C).Items
            .Cells(C + 1, 2).Value = Ranking(C).Values
        Next
        .Columns("A:B").Select              '結果をソート(降順)
        Selection.Sort Key1:=.Range("B1"), Order1:=xlDescending
        .Range("A1").Select
    End With
    
End Sub

これでレコード数を順次更新しながらカウントはできます。

上記のコードですと、ひとつめのシート(Sheet1)にデータがあり、ふたつめのシート(Sheet2)に結果が出力されます。