Select in VBA Error 1004

Asked

Viewed 112 times

0

good night

My spreadsheet has a button where it will open a Form. This Form has a LIST VIEW where the data of a sheet of the spreadsheet is loaded. So far so good. Also in Form has a button to filter the data of LIST VIEW, based on the values selected in a COMBO BOX. The code below is from the filter button, but when it runs, it shows an error in the Thisworkbook.Sheets line ("FILTER"). Range(Rangedevalues). Select

You could tell me why?

Below is the code of the filter button:

Private Sub btnFiltrar_Click()

    'LIMPA A ABA FILTRO
    ThisWorkbook.Sheets("FILTRO").Cells.Clear

    'COPIA A ABA BASE DE DADOS PARA A ABA FILTRO
    Dim ultima_linha As Long
    ultima_linha = ThisWorkbook.Sheets("BASE_DADOS").Cells(Rows.Count, 1).End(xlUp).Row

    Dim RangeDeValores As String
    RangeDeValores = "A1:G" & CStr(ultima_linha)

    ThisWorkbook.Sheets("BASE_DADOS").Range(RangeDeValores).Copy
    ThisWorkbook.Sheets("FILTRO").Range("A1").PasteSpecial xlAll
    Application.CutCopyMode = False

    'FILTRA NA ABA FILTRO OS ITENS SELECIONADOS NO LIST BOX DE ITENS
    Dim listaItens(99999)

    For i = 0 To fPesquisar.lbItens.ListCount - 1
        If fPesquisar.lbItens.Selected(i) = True Then
            listaItens(i) = fPesquisar.lbItens.List(i)
        End If
    Next i

    ThisWorkbook.Sheets("FILTRO").Range(RangeDeValores).Select

    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=listaItens, Operator:=xlFilterValues


End Sub
  • Tried to give a debug.print RangeDeValoresto see what’s in there?

  • You don’t need the Rangedevalues variable (I think it’s a problem because it’s string) ThisWorkbook.Sheets("BASE_DADOS").Range(RangeDeValores).Copy for ThisWorkbook.Sheets("BASE_DADOS").Range(Cells(1,1),Cells(ultima_linha,7)).Copy

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.