none
Como selecionar planilha no VBA quando o nome da planilha varia RRS feed

  • Pergunta

  • Pessoal, boa tarde.

    Preciso de uma ajuda para automatizar meu código, gostaria de selecionar uma planilha em que o nome da planilha pode variar. No exemplo abaixo, para ajustar um malote tive que selecionar a sheet com o nome certinho da planilha. Contudo, o nome do arquivo importado, que é o nome da planilha que vou utilizar, corresponde a um prefixo(CETIP21) e um sufixo(DRESUMOEMIS-IMOB) sempre iguais, porém a data (CETIP21_200713_DRESUMOEMIS-IMOB) varia conforme o arquivo selecionado. Seria interessante, ao clicar no botão "ajustar malote B3", que o código VBA me ajudasse a abrir um combo com as opções de planilha dentro do workbook e selecionasse a planilha correta para ajustar ou selecionar a planilha correta pelo prefixo, assim independente do dia trabalhado não precisaria ajustar no VBA para buscar a planilha correta. Conseguem me ajudar?


    Sub ajustarmaloteB3()
    '
    ' Ajustar Malote B3
    '

    '
    Sheets("CETIP21_200713_DRESUMOEMIS-IMOB").Activate

        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Font.Bold = True
        Selection.AutoFilter
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("E:E").Select
        Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("E:E").EntireColumn.AutoFit
        Columns("H:H").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        Columns("J:J").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        Columns("M:M").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 19
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 22
        Columns("AA:AA").Select
        Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("AD:AD").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 24
        ActiveWindow.ScrollColumn = 25
        ActiveWindow.ScrollColumn = 24
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 19
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
       
    End Sub



    sábado, 25 de julho de 2020 18:00

Todas as Respostas

  • Sub ajustarmaloteB3()
    '
    ' Ajustar Malote B3
    '
    
    '
    DIM DT AS STRING
    
    DT = INPUTBOX("INFORME A DATA")
    
    
    Sheets("CETIP21_" & DT & "_DRESUMOEMIS-IMOB").Activate
    
        Range(Selection, Selection.End(xlToRight)).Select
        Selection.Font.Bold = True
        Selection.AutoFilter
        With ActiveWindow
            .SplitColumn = 0
            .SplitRow = 1
        End With
        ActiveWindow.FreezePanes = True
        Columns("B:B").Select
        Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("E:E").Select
        Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("E:E").EntireColumn.AutoFit
        Columns("H:H").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        Columns("J:J").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        Columns("M:M").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 19
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 22
        Columns("AA:AA").Select
        Selection.TextToColumns Destination:=Range("AA1"), DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
            Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
            :=Array(1, 5), TrailingMinusNumbers:=True
        Columns("AD:AD").Select
        Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 24
        ActiveWindow.ScrollColumn = 25
        ActiveWindow.ScrollColumn = 24
        ActiveWindow.ScrollColumn = 23
        ActiveWindow.ScrollColumn = 22
        ActiveWindow.ScrollColumn = 21
        ActiveWindow.ScrollColumn = 20
        ActiveWindow.ScrollColumn = 19
        ActiveWindow.ScrollColumn = 18
        ActiveWindow.ScrollColumn = 17
        ActiveWindow.ScrollColumn = 16
        ActiveWindow.ScrollColumn = 15
        ActiveWindow.ScrollColumn = 14
        ActiveWindow.ScrollColumn = 13
        ActiveWindow.ScrollColumn = 12
        ActiveWindow.ScrollColumn = 11
        ActiveWindow.ScrollColumn = 10
        ActiveWindow.ScrollColumn = 9
        ActiveWindow.ScrollColumn = 8
        ActiveWindow.ScrollColumn = 7
        ActiveWindow.ScrollColumn = 6
        ActiveWindow.ScrollColumn = 5
        ActiveWindow.ScrollColumn = 4
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
       
    End Sub


    A melhor forma de agradecer e votar como util e / ou marcar como resposta. Anderson Diniz diniabr2011@gmail.com

    • Sugerido como Resposta AndersonFDiniz2 segunda-feira, 27 de julho de 2020 01:18
    segunda-feira, 27 de julho de 2020 00:40
  • Private Sub ComboBox1_Change()
    If Me.ComboBox1.ListIndex >= 0 Then
    
    'Me.TextBox1.Text = Me.ComboBox1.Value
    
    Else
    
    'Me.TextBox1.Text = ""
    
    End If
    
    
    End Sub
    
    Private Sub CommandButton1_Click()
    Dim linha As Long
    
    
    
    If Me.ComboBox1.ListIndex >= 0 Then
    
    linha = 2
    
    While Sheets(Me.ComboBox1.Value).Range("A" & linha).Value <> ""
    
    
    linha = linha + 1
    
    
    Wend
    
    Sheets(Me.ComboBox1.Value).Range("A" & linha).Value = Me.TextBox1.Text
    
    
    End If
    
    End Sub
    
    Private Sub UserForm_Initialize()
    
    Dim P As Integer
    
    
    For P = 1 To ThisWorkbook.Sheets.Count
    
    If Sheets(P).Name <> "Plan8" Then
    
    If Sheets(P).Name <> "Plan7" Then
    
    Me.ComboBox1.AddItem Sheets(P).Name
    
    End If
    
    End If
    
    Next P
    
    
    End Sub
    


    A melhor forma de agradecer e votar como util e / ou marcar como resposta. Anderson Diniz diniabr2011@gmail.com

    • Sugerido como Resposta AndersonFDiniz2 segunda-feira, 27 de julho de 2020 01:18
    segunda-feira, 27 de julho de 2020 01:18
  • https://youtu.be/k__GdinXyKA

    A melhor forma de agradecer e votar como util e / ou marcar como resposta. Anderson Diniz diniabr2011@gmail.com

    • Sugerido como Resposta AndersonFDiniz2 segunda-feira, 27 de julho de 2020 01:19
    segunda-feira, 27 de julho de 2020 01:19
  • https://youtu.be/_42-CkIiq5o

    A melhor forma de agradecer e votar como util e / ou marcar como resposta. Anderson Diniz diniabr2011@gmail.com

    • Sugerido como Resposta AndersonFDiniz2 segunda-feira, 27 de julho de 2020 01:27
    segunda-feira, 27 de julho de 2020 01:27