Problem in autofilter procedure

[Guest]

I did the procedure below step by step yesterdey, to filter data in a
sheet with some condition, and after filter it`ll copy and paste in another
two sheets, but with differents columns. it worked very well yesterday, but
today it isn`t work, and I don`t know why. I checked each line and i think
that the line it ins`t work is activesheet.paste, but i`m not sure, please
someone could help me?

Private Sub CommandButton3_Click()
Dim mes As String
Dim executor As String
Dim tipo As String
Application.ScreenUpdating = False
Sheets("ABERTURA").Select
On Error Resume Next `check if the cell that i`ll paste is empty
If ActiveSheet.Range("H6").Value <> "" Then `if isn`t, it`ll ask if it
could delet.
mensagem = MsgBox("Deseja Sobrepor relatorio anterior?", vbOKCancel)
If mensagem = vbOK Then
LIMPAR_RELATORIO `clear the cell before to paste
Else
Exit Sub
End If
End If
Cells(14, 4).Activate
mes = ActiveCell
executor = Sheets("ABERTURA").Range("D10").Value
tipo = Sheets("ABERTURA").Range("D12").Value
Sheets(mes).Select `the data that i want to filter is in this sheet
ActiveSheet.Range("A3:E111").Select
Selection.AutoFilter
Selection.AutoFilter field:=2, Criteria1:=tipo
Selection.AutoFilter field:=3, Criteria1:=executor
Selection.AutoFilter field:=5, Criteria1:="-"
Selection.Copy
Sheets("RELATORIO").Select `i`ll paste in this sheet after filter
Selection.Paste
Sheets(mes).Select
Application.CutCopyMode = True
Selection.AutoFilter field:=2
Selection.AutoFilter field:=3
Selection.AutoFilter field:=5
Selection.Range("A4").Select
Sheets("RELATORIO").Select
ActiveSheet.Range(Selection, Selection.End(xlDown)).Select `after paste
it`ll select just one column to paste in another sheet
Selection.Copy
Sheets("ABERTURA").Select `
ActiveSheet.Range("H6").Select
ActiveSheet.Paste
Sheets("RELATORIO").Select
Application.CutCopyMode = False
ActiveSheet.Range("B2:F111").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = 2
Selection.ClearContents
ActiveSheet.Range("B2").Select
Sheets("ABERTURA").Select
ActiveSheet.Range("H6").Select
Application.ScreenUpdating = True
End Sub

I`m not expert in excel programming, but i try to understend how it works.
thanks everybody for your help.
regards!
Ana Paula

 

[Johz]

What error does it give or what happens unexpectedly? I see you use a lot of selections, but it would be faster to use only the range object. Example:

with ActiveSheet.Range("A3:E111")
.AutoFilter
.AutoFilter field:=2, Criteria1:=tipo
.AutoFilter field:=3, Criteria1:=executor
.AutoFilter field:=5, Criteria1:="-"
.copy
end with

also read about the difference between the activate and select -methods. Also, I assume LIMPAR_RELATORIO is a procedure to empty the target range before copying?

 

[Guest]

My problem is over, I just change de line Selection.Paste to
ActiveSheet.Paste and now it works well. I don`t know explain why but it
works.

thanks.

"Ana Paula" escreveu: