Filter data within multiple columns and copy results to new sheet

[Amadan90]

Hi there,

I'm rather new to VBA and I've been trying to figure out how I get my code to work properly, but after trying several things I can't seem to find an answer. I need a push in the right direction.

Below is the code I've got so far, which probably can be simplified (and needs a lot more ofcourse), and also a part of the data I need sorted out. I want to extract different kind of values to new sheets. I'll try to provide as much information as I think is relevant.

Info on the file:

• Always 12 columns wide, rows vary around 20.000.
• Consignment (2000+ unique values, all in format ?????-????? with occasional exceptions)
• Transport (1000+ unique values, all in format ??????-???? + blanks)
• Status_condt_1 (50+ unique values, only 1 or 2 letters + Z1 Z2)
• Handl_pers (50+ unique values + blanks)
• Description (50+ unique values, with "in conformity" as correct value

What I need:
For every different value in column A

• if value column L is "items not scanned" copy entire row to new sheet
• if value column L is "missing package" copy all rows of the corresponding value in column A to new sheet

Filter

• column B for specific data + column J for data and copy all rows to new sheet

This is just a small part I want to achieve with this code. If any of you could help me out with this part I assume (or at least I hope) I can create the rest myself.

Thanks in advance!



Sub subFilter()
Dim wb1 As Workbook, sh1 As Worksheet, _
c As Range, cCon As Long, rng As Range
Set wb1 = Workbooks("podcon")
Set sh1 = wb1.Sheets(1)
Set rng = ActiveSheet.UsedRange
cCon = Cells.Find("?????-?????", ActiveCell, , xlWhole, xlByRows, _
xlNext, , , True).Row
Set c = Cells.Find("?????-?????", ActiveCell, , xlWhole, xlByRows, _
xlNext, , , True)
Range("A1").Select
Selection.AutoFilter
Range("$A$2:$A$" & cCon).AutoFilter Field:=1, Criteria1:=c
Range("L1").Select
'remove filter
Selection.AutoFilter
End Sub

CONSIGNMENT	TRANSPORT	STATUSTYPE	STATUS_POINT	NO	STATUS_CONDT_1	OBSDATE	OBSTIME	REG_TIME	HANDL_PERS	QUALITY	DESCRIPTION
003570659323 <tbody> </tbody>		A	VEN	13	SX	2014-07-02	1802	1802		41	SCANNING CANCELLED
003570659323 <tbody> </tbody>		A	VEN	13	Z	2014-07-02	1802	1802		41	IN CONFORMITY
ALGRP-54592 <tbody> </tbody>		D	VEN	1	Z	2014-07-02	2359	654		31	IN CONFORMITY
ALGRP-54592 <tbody> </tbody>	TRP591-0351 <tbody> </tbody>	L	VEN	1	Z	2014-07-02	506	507		42	IN CONFORMITY
ARLA9-19656 <tbody> </tbody>		D	VEN	2	Z	2014-07-02	1035	1108		31	IN CONFORMITY
ARLA9-19656 <tbody> </tbody>		A	VEN	2	Z	2014-07-02	2334	2347		41	IN CONFORMITY
ARLA9-19656 <tbody> </tbody>	TRP441-0340 <tbody> </tbody>	L	VEN	2	Z	2014-07-02	455	527		42	IN CONFORMITY
ARLA9-19656 <tbody> </tbody>	TRP441-0340 <tbody> </tbody>	SD	MSNL	2	Z	2014-07-02	921	1036	GEERT THISSEN	58	IN CONFORMITY
ARLA9-19656 <tbody> </tbody>	TRP441-0340 <tbody> </tbody>	D	MSNL	2	Z2	2014-07-02	1022	1051	GEERT THISSEN	77	ITEMS NOT SCANNED
BAIEU-15237 <tbody> </tbody>	TRP481-0350 <tbody> </tbody>	SC	MSNL	1	Z	2014-07-02	1114	1126	PIETER DRIEGEN	58	IN CONFORMITY <tbody> </tbody>
BAIEU-15237 <tbody> </tbody>	TRP481-0350 <tbody> </tbody>	C	MSNL	1	Z	2014-07-02	1114	1131	PIETER DRIEGEN	76	IN CONFORMITY <tbody> </tbody>
BAIEU-15237 <tbody> </tbody>	TRP481-0350 <tbody> </tbody>	A	VEN	1	Z	2014-07-02	1840	1849		42	IN CONFORMITY <tbody> </tbody>
BAIEU-15277 <tbody> </tbody>	TRP541-0352 <tbody> </tbody>	SC	MSNL	1	Z	2014-07-02	1517	1534	ROB MAESSEN	58	IN CONFORMITY <tbody> </tbody>
BAIEU-15277 <tbody> </tbody>	TRP541-0352 <tbody> </tbody>	C	MSNL	1	Z2	2014-07-02	1523	1534	ROB MAESSEN	76	ITEMS NOT SCANNED <tbody> </tbody>
BAIEU-15277 <tbody> </tbody>	TRP541-0352 <tbody> </tbody>	A	VEN	1	Z	2014-07-02	1741	1743		42	IN CONFORMITY <tbody> </tbody>
BAIEU-15330 <tbody> </tbody>		C	VEN	4	Z	2014-06-30	1700	1551		11	IN CONFORMITY
BAIEU-15330 <tbody> </tbody>		A	VEN	4	Z	2014-07-01	1949	1949		41	IN CONFORMITY <tbody> </tbody>
BAIEU-15330 <tbody> </tbody>		A	VEN	4	Z	2014-06-30	2216	2216		41	IN CONFORMITY <tbody> </tbody>
BAIEU-15330 <tbody> </tbody>	TRP009-0337 <tbody> </tbody>	A	VEN	4	Z	2014-06-30	2119	2119		42	IN CONFORMITY <tbody> </tbody>
BAIEU-15342		A	VEN	1	M	2014-07-02	524	525		41	MISSING PACKAGE
BAIEU-15342		A	VEN	1	M	2014-07-02	541	541		41	MISSING PACKAGE
KDENL-85052		NC	VEN	1	NC	2014-07-02	1655	1705		11	NOT COLLECTED - OUT OF HOURS
KDENL-85052		SC	MSNL	1	Z	2014-07-02	1028	1040	PAUL VAN DE VEN	58	IN CONFORMITY
KDENL-85052		C	MSNL	1	Z	2014-07-02	1029	1050	PAUL VAN DE VEN	76	IN CONFORMITY
KDENL-85052		A	VEB	1	Z	2014-07-02	1911	1918		42	IN CONFORMITY

<tbody>
</tbody>