Results 1 to 1 of 1
2012-07-24, 10:52 #1
- Join Date
- Jul 2012
- Thanked 0 Times in 0 Posts
VBA macro to filter data and move to another sheet
I am working on creating a macro that will filter column O (Bill Type) to Fac and column X (Event Code) to KC09, KC21, and KC99…
From my research it appears that VBA will only allow two filters. I am attempting to create a macro that will change any cell with KC09, KC21, and KC99 to “True” and then move to the second sheet (FilterData) in the workbook. I am running into a problem with the macro treating the data (KC09, KC21, and KC99) as specified cells rather than the value or data of a cell. Below is the macro I have been working with in order to bypass the limit of two filters per macro.
Any help you can offer is greatly appreciated.
I am pretty new to macros and do not understand all of the language but have always been able to find something on the internet and be able to tweak it to do what I want... This one however, is a bit over my head.
I need the macro to filter the data, copy and paste to another worksheet that it creates and then delete the data from the original worksheet if at all possible. I tried to upload a sample worksheet but the spreadsheet is too big...UGH! I cant get it small enough without losing data that needs to be there... I need it to loop through all lines on the spreadsheet which differ in number from day to day. I also need it to stop after it reaches a blank line. Will need it to work from excel 2010 although my spreadsheet is saved in a previous version - my work computer is on 2010. My home is not...
It's column A-X headers listed below - all columns have info... Event codes are KC01, KC05, KC07, KC08, KC09, KC12, KC13Unit|Team|icn|doc|user|id|Name|Claim|prov|impa ct|loc|state|org|emp|type|charge|carrier|inv days|vend days|pc|receipt|updated|processed|event code
This is the macro I have been tweaking... But I just cannot get it to work correctly.
Application.ScreenUpdating = False
Worksheets("Inventory").Range("X2:X2000" & lastrow).Formula = _
Worksheets("Inventory").Range("A1:Y1" & lastrow).AutoFilter _
'''Range("B2:F" & LastRow).Select
Worksheets("Inventory").Range("B2:F" & lastrow).Copy _
Worksheets("Inventory").Range("A1" & lastrow).AutoFilter
Application.ScreenUpdating = True