Filter using VBA

[ChrisKad]

Hi,

I'm trying to filter, copy filtered data to a new sheet, and delete the filtered data from the original sheet. I have no idea how to do that.

All I know to do is add filters to columns using the code:

Sub TurnAutoFilterOn()

If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1:M1").AutoFilter
End If

End Sub


I attached a file so you can see what I'm trying to do.

I want to filter first of all column "M". Each time I see MHO (which is part of the data in column M), I want the code to copy that name (and whole row), paste it on another sheet and delete the lines it was on from the original sheet. MHO could appear in different lines so I need the code to be able to find this name anywhere in the column and copy it and delete it after from the original sheet.

Secondly, I would like to filter column B (mois) which is "months". I'm supposed to split the information in 3 different sheets, hence; 06=June, 07=July, 08=August. So after the code has filtered 06 for example, I would like it to copy and paste on a new sheet and then delete the data from the original sheet and then obviously the same thing for the subsequent months. This is a task I have to do on a weekly basis and I tried using a macro but it's not advanced enough to do specific functions like this advanced filter.

Your help will be much appreciated. Thank you

PS. I am usin excel 2010

[Pratts]

HI ChrisKad,

I hope this wiil help.

ActiveSheet.Range("M:M").AutoFilter Field:=3, Criteria1:="=MHO"
ActiveSheet.Range("M1", ActiveSheet.Range("M2").End(xlDown)).Select
Selection.Copy
ActiveWorkbook.Sheets.Add
ActiveWorkbook.ActiveSheet.Name = "New sheet"
ActiveWorkbook.Range (A1)
ActiveSheet.Paste
ActiveSheet.Columns("A:A").AutoFit

Thanks!!
Pratts

[ChrisKad]

Thanks a lot Pratts

I tried the code and I changed it slightly:

Sub TurnAutoFilterOn()

If Not ActiveSheet.AutoFilterMode Then
ActiveSheet.Range("A1:M1").AutoFilter
End If

ActiveSheet.Range("M:M").AutoFilter Field:=13, Criteria1:="=MHO"
ActiveSheet.Range("M1", ActiveSheet.Range("A2:M2").End(xlDown)).Select
Selection.Copy
ActiveWorkbook.Sheets.Add
ActiveWorkbook.ActiveSheet.Name = "New sheet"
ActiveWorkbook.Range ("A1")
ActiveSheet.Paste
ActiveSheet.Columns("A:A").AutoFit

End Sub

The part I put in red is the reason why it's not fully working. It's highlighted in yellow in the vba code sheet. However, MHO has been filtered but it just won't copy to "New sheet". Do you know what the problem could be?Thanks

[snb]

Sub M_snb()
   with ActiveSheet.cells(1).currentregion.resize(,13)
     .AutoFilter 13, "=MHO"
     .Offset(1).cut sheet2.cells(1)
     .autofilter
   end with
End Sub

[Pratts]

ActiveWorkbook.Range (A1).Select

Please try this.

Thanks!!
Pratts

[snb]

Always avoid 'Select' and 'Activate ' in VBA.

[ChrisKad]

Thanks a lot.

I will try them and get back to you.

snb why should I avoid them? Would just like to know. I'm a beginner in VBA so I just want to understand why.

Thank you

[snb]

They are unnecessary and make you macro very slow.

[jolivanes]

snb said it all but here is some info for you to read.

http://www.businessprogrammer.com/po...-using-select/


http://www.excelforum.com/excel-prog...id-in-vba.html


https://blogs.office.com/2009/03/12/...est-practices/

[ChrisKad]

Thanks a lot guys.

I read the links. I'm really a baby in VBA so it may take me a while to understand even the basics.

Still working on the above codes that I posted above, but only when I have a little free time. However, I will let you know if it's solved.

Thanks again for your help