Pelo descrito e rotina mostrada, podemos apenas intuir que o erro deva ser no tamanho do Array Campos, provavelmente alguma linha "splitada" não atinge 28.
Esperimente algo do tipo: For j=1 to ubound(Campos)

Olá, Reinaldo.

Por Function não funcionou. Por sub funcionou. O problema deveria estar na variável que definia onde colar os dados. Agora ficou meio "congelado", mas funcionando.

Sub ImportarTXT()
    Dim Campos As Variant
    Dim i As Long, j As Long
    Dim arquivo As String
    Dim arquivos As String
    Close #1
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    'Contador de linhas
    i = 1
    j = 1
    
    'abre o arquivo texto
    
    For i = 3 To 43
    'define contador offset de colunas
    k = 2
    'ativa a célula
    Cells(i, 6).Activate
    'define o arquivo
    'arquivos = Cells(i, 6).Value
    arquivo = "C:UsersgiovaDocumentsDADOS_INMET" & Cells(i, 6).Value
    Open arquivo For Input As #1
    'Captura 1 linha e armazena na variável Linha
    Line Input #1, Linha
    'separa os campos e armazena na variável "Campos"
    Campos = Split(Linha, " ")
    'Distribui os campos na planilha
        For j = 1 To UBound(Campos)
            ActiveCell.Offset(0, k).Value = Campos(j)
            k = k + 1
        Next j
    'fecha o arquivo texto
    Close #1
    Next i
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub