Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in autofilter procedure
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem in autofilter procedure
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: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
call procedure problem | Excel Discussion (Misc queries) | |||
Worksheet/General Procedure problem | Excel Discussion (Misc queries) | |||
ADO recordset problem with stored procedure | Excel Programming | |||
Problem with Dynamically Writing Event Procedure | Excel Programming | |||
ComboBox Event Procedure problem | Excel Programming |