Sheet activate

Good afternoon,


My macro generates a spreadsheet. How do I activate this sheet determines worksheet to check and make value comparisons. I'm using this:

Application.Workbooks (Spath & filename & ". Xls"). Worksheets ("AF"). Select

But generates runtime error 9 - Subscript out of range. I thought about closing the spreadsheet the macro after taking her values​​, but also the macro closes well. As macro to show that the cells in question are given sheet of the spreadsheet created and saved by the macro? Another question is also: Cells (1,3). Value also applies when you are in the cell for text? Thank you!

The code is:

Private Sub ImportarTXTListaMaterial_Click()
    Dim Campos As Variant
    Dim arra(941), Arquivotxt, Arquivoxls, ca, cb, cc, cd, ce, aspas, layermm As String
    Dim layeraspas, responsavel, responsavel1, responsavel2, especialidade, especialidade1 As String
    Dim especialidade2, strNome, strName, var1, var2, sPath, auxfilename As String
    Dim i, j, K, a, tamanho, comsPath, comArquivotxt, comauxfilename, aux1, aux2, aux3 As Long
    Dim contador, lin, z, quantidade, contresp, intErro As Integer
        contador = 0
    Dim oApp  As Excel.Application
    Dim oWks As Excel.Workbook
    Dim wsOrigem As Worksheet
    Dim wsDestino As Worksheet
    Dim filename As Variant
   
    'Open file explorer
    Arquivotxt = Application.GetOpenFilename("Arquivos Texto(*.txt), *.txt")
    'abre o arquivo texto
    Open Arquivotxt For Input As #1
    
    K = 2
     While Not (EOF(1))
        Line Input #1, linha
        Campos = Split(linha, ";")
        Dim Palavras(1) As String
        For j = 0 To UBound(Campos)
           Sheets("Plan3").Cells(K, j + 1).Value = Campos(j)
        Next
        K = K + 1
    Wend
    'Close txt file
    Close #1
    'For new excel file
    comArquivotxt = Len(Arquivotxt)
    sPath = "C:\Autodesk\AutoCAD_2012_English_Win_64bit\Minhas Rotinas\"
    comsPath = Len(sPath)
    auxfilename = Right(Arquivotxt, (comArquivotxt - comsPath))
    comauxfilename = Len(auxfilename)
    filename = Left(auxfilename, (comauxfilename - 4))
    'New excel file
    Set oApp = New Excel.Application
    Set oWks = Workbooks.Add
    Worksheets("Plan1").Activate
    'Save new excel
    oWks.SaveAs "C:\Autodesk\AutoCAD_2012_English_Win_64bit\Minhas Rotinas\" & filename & ".xls"
    Set wsOrigem = Workbooks("Lista_De_Material_Modelo").Worksheets("Plan3")
    Set wsDestino = Worksheets("Plan1")
    With wsOrigem
        .Range("A2:A20000").Copy Destination:=wsDestino.Range("A2:A20000")
        .Range("A2:A20000").Delete
    End With
    'Copy for new excel file
    ThisWorkbook.Sheets("INC").Copy Before:=oWks.Sheets(1)
    ThisWorkbook.Sheets("E.S. E A.P.").Copy Before:=oWks.Sheets("INC")
    ThisWorkbook.Sheets("A.F.").Copy Before:=oWks.Sheets("E.S. E A.P.")
    ThisWorkbook.Sheets("A.Q.").Copy Before:=oWks.Sheets("A.F.")
    'ThisWorkbook.Close SaveChanges:=True
   
    For l = 2 To 20000
        ca = wsDestino.Cells(l, 1)
        aux1 = InStr(1, ca, "Tubo", 1)
        quantidade = CInt(Left(ca, (aux1 - 2)))
        aux2 = InStr(1, ca, "mm", 1)
        aspas = Chr(34)
        aux3 = InStr(1, ca, aspas, 1)
        tamanho = CInt(Len(ca))
        layermm = Right(ca, ((tamanho - aux2) - 2))
        layeraspas = Right(ca, ((tamanho - aux3) - 1))
        cb = Right(ca, ((tamanho - aux1) + 1))
        ce = Left(ca, ((tamanho - aux1) + 1))
        cc = layermm
        cd = layeraspas
        If cd = "INC" Then
            i = 1
            j = 182
            z = 0
            For i = 1 To 9
                If arra(i) = cb Then
                    Sheets("INC").Activate
                    Cells(j, 5).Value = z + quantidade
                End If
                j = j + 1
            Next
        ElseIf cc = "E.S. E A.P." Then
            i = 10
            j = 102
            z = 0
            For i = 10 To 14
                If arra(i) = cb Then
                    Sheets("E.S. E A.P.").Activate
                    Cells(j, 5).Value = z + quantidade
                End If
                j = j + 1
            Next
        ElseIf cc = "A.F." Then
            Application.Workbooks(sPath & filename & ".xls").Worksheets("A.F.").Select
            With Selection
            
            i = 15
            j = 196
            z = 0
            For i = 15 To 23
                If Cells(j, 2).Value = cb Then
                    'Sheets("A.F.").Activate
                    Application.Workbooks(sPath & filename & ".xls").Worksheets("A.F.").Cells(j, 5).Value = z + quantidade
                End If
                j = j + 1
            Next
            End With
        ElseIf cc = "A.Q." Then
            i = 24
            j = 231
            z = 0
            For i = 24 To 32
                If arra(i) = cb Then
                    Sheets("A.Q.").Activate
                    Cells(j, 5).Value = z + quantidade
                End If
                j = j + 1
            Next
        End If
    Next 
End Sub

the worksheet has 4 sheets of macro calls: INC, ES And A.P., A.F., A.Q.. I make a copy to the new worksheet with:
ThisWorkbook.Sheets("INC").Copy Before:=oWks.Sheets(1)
ThisWorkbook.Sheets("E.S. E A.P.").Copy Before:=oWks.Sheets("INC")
ThisWorkbook.Sheets("A.F.").Copy Before:=oWks.Sheets("E.S. E A.P.")
ThisWorkbook.Sheets("A.Q.").Copy Before:=oWks.Sheets("A.F.")

if statements inside the for-loop are for this new spreadsheet and copied to the new sheets. So I have to first enable the worksheet and then each sheet in order to value their cell comparable with the variables cd and cc.

Owww, that is correct! But, i need to compare the value in the variable cb (string) with text a cell on the sheet, i did so:
If Cells(j, 2).Value = cb Then
Cells(j, 5).Value = z + quantidade
End If

But not is correct, being the value of the cell text. Can i use .Value for obtain the value inside the cell or not?

I declared variable auxiliar like variant.

Finally, i can! I wasn´t showing which sheet i wanted to work.
The code stayed:

auxiliar = Sheets("E.S. E A.P.").Cells(j, 2)


Thanks so much Norie for your help! I will finish the code and i will post here soon!!

Now i understood. i have 3 merged cells, B, C, and D collumns, but the text is at the B column. The return value is empty, I tested assigning the value of cell for variable auxiliar and to compile the auxiliar values stay empty.