Advanced Filter to Separate Sheet & Auto Updating

In the absence of a sample workbook, data, code or selection criteria ... let's assume you have a workbook with a Source Worksheet and a Target worksheet. Let's also assume that your Source worksheet has populated columns A to K, number of rows is relevant, but not for this example. For the sake of argument, let's say you have a header with letters A to K as the column headers, and the data is in rows 2 to 14.

In the Target worksheet, put the letters A,B,H,I, and G, in that order, in cells A1 to E1.

Now, starting with the Source worksheet, record a macro. You first need to select the Target sheet, because you can only output the filtered data to the selected sheet. Then choose Data | Advanced Filter on the ribbon. Choose A1:K14 on the Source worksheet as the source data, and A1:E1 on the Target worksheet for the filtered output. Click OK. Stop recording.

You get this:

Please Login or Register  to view this content.

So, that needs tidying up to avoid the selections and to determine how many rows of data are in the source range. But, that's basically it. Start with the Target sheet is key, and specify the columns you want, in the order that you want them.

Regards, TMS

AFAIK, the source range for Advanced Filter has to be a continuous range with unique headers for each column.
What can be done (via macro) is to use Advanced filter to move the whole range of data and then delete or re-order the desired columns.

TMS,
From VBA, Advanced Filter Copy To will work with any sheet active as long as the source, criteria and destination ranges are fully qualified.

Please Login or Register  to view this content.

@Mike: yes, I know ... that would be part of the tidying up and making it flexible/generic. The point I was making was how to work through the manual process to get a macro that achieves the basic requirement.

You don't need to filter (copy) the entire range from one sheet to another. You can select the columns you want, in the order that you want them, simply by typing the column headers into the target sheet and then refer to that (range) in the dialogue box.

That's why I said:

In the Target worksheet, put the letters A,B,H,I, and G, in that order, in cells A1 to E1.

As it's being done in VBA, you could clear the sheet and then build the header row before applying the Advanced filter. Same with the criteria; you can build that in a separate sheet in the code and then refer to the criteria range.

Very cool to know about the re-organizing the columns, thanks.

@Mike:

Source:

	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D	E	F	G	H	I	J	K
2	A2	B2	C2	D2	E2	F2	G2	H2	I2	J2	K2
3	A3	B3	C3	D3	E3	F3	G3	H3	I3	J3	K3
4	A4	B4	C4	D4	E4	F4	G4	H4	I4	J4	K4
5	A5	B5	C5	D5	E5	F5	G5	H5	I5	J5	K5
6	A6	B6	C6	D6	E6	F6	G6	H6	I6	J6	K6
7	A7	B7	C7	D7	E7	F7	G7	H7	I7	J7	K7
8	A8	B8	C8	D8	E8	F8	G8	H8	I8	J8	K8
9	A9	B9	C9	D9	E9	F9	G9	H9	I9	J9	K9
10	A10	B10	C10	D10	E10	F10	G10	H10	I10	J10	K10
11	A11	B11	C11	D11	E11	F11	G11	H11	I11	J11	K11
12	A12	B12	C12	D12	E12	F12	G12	H12	I12	J12	K12
13	A13	B13	C13	D13	E13	F13	G13	H13	I13	J13	K13
14	A14	B14	C14	D14	E14	F14	G14	H14	I14	J14	K14

Target:

	A	B	C	D	E
1	A	B	H	I	G
2	A2	B2	H2	I2	G2
3	A3	B3	H3	I3	G3
4	A4	B4	H4	I4	G4
5	A5	B5	H5	I5	G5
6	A6	B6	H6	I6	G6
7	A7	B7	H7	I7	G7
8	A8	B8	H8	I8	G8
9	A9	B9	H9	I9	G9
10	A10	B10	H10	I10	G10
11	A11	B11	H11	I11	G11
12	A12	B12	H12	I12	G12
13	A13	B13	H13	I13	G13
14	A14	B14	H14	I14	G14

@Mike: thanks for the feedback and rep. Just need another 26 and I have all the sixes!

This will create individual lists for each entry in the Lookup Lists worksheet.

It does not output to: 'NEW Attorney Caseload List'!$A$1:$E$1. It is not the right format; it needs to be a list. And it would over-write the data that is already there. As a separate exercise, you could copy the separate output sheets to the 'NEW Attorney Caseload List' (and delete the [temporary] output sheet), but that would be a separate exercise ... for someone else.

Please Login or Register  to view this content.

Oh, and it doesn't update automatically. It will clear and refresh the output so you could have a button that you press.

Is this resolved?