Advanced Filter multiple sheets back to one, how can i do it

[Chris S]

I am trying to consolidate filtered data from 5 sheets on to a master,
I thought I cold use advanced filter to get the data but I cant get it to work for more than one sheet.

if it can , can some please explain how to do it

So now I am trying to use macros to filter and consolidate on one sheet,

But I dont know how to automate a copyto on the master sheet ie a copyto("variable") so that the next sheet is copied at the end of the previous sheet.

Please help....

 

[Yogi Anand]

Re: Advanced Filter multiple sheets back to one, how can i d

Hi Chris:

Welcome to MrExcel Board!

Just thinking aloud -- how about if you use AdvancedFilter to filter the records from one sheet, then at the end of the filtered record-set, write the filtered records from the second sheet, and at the end of the second filtered record-set, and so on for all the 5 sheets. In the records you have filtered there would be 5 sets of Field Headers -- just retain the top one and delete the remaining 4.

And of course this process can be automated using a macro.

 

[Chris S]

Re: Advanced Filter multiple sheets back to one, how can i d

Yogi

Thanks for the reply

You make it sound simple, but I am not sure how to automate the start of the second / third filter set positions, which are dependant on the number of returns from the filter before it?

Do you have an example

Thanks

Chris

 

[Yogi Anand]

Re: Advanced Filter multiple sheets back to one, how can i d

Hi Chris:

You can locate the last row of the records after filtering by counting the number of rows down from the first record with a formula based approach, or by using End(xlUp) from row 65536 using VBA

You have the strategy all laid out, now what is left is execution. Give it a shot, and post back if you run into a problem with what you have got and let us take it from there.

It just sounds complicated -- but I think it should workout fairly well.

 

[Chris S]

Re: Advanced Filter multiple sheets back to one, how can i d

Yogi

I must be thick but I am still struggling here, I managed to show the cell position of the first blank cells in a list ie:

=(CELL("address",(OFFSET(A1,COUNTA(A:A),0))))

(I dont really undersatnd the formula just trial and error)

it returns :$a$12 as being the first blank cell which is correct and moves as I enter text , I want to apply a CopyToRange:= to equal this result

but it reads the cell where this function is and not the result.

How do I make the macro accept the result.

Is there another way to do this.

Any help welcome, I am close but stalled......

Thanks

Chris

 

[Yogi Anand]

Re: Advanced Filter multiple sheets back to one, how can i d

Hi Chris:

Are you planning to do the filtering and copying_to from several (say 5) sheets using a macro or are you planning to do it manually one sheet at a time?

 

[Yogi Anand]

Re: Advanced Filter multiple sheets back to one, how can i d

Yogi

I must be thick but I am still struggling here, I managed to show the cell position of the first blank cells in a list ie:

=(CELL("address",(OFFSET(A1,COUNTA(A:A),0))))

(I dont really undersatnd the formula just trial and error)

it returns :$a$12 as being the first blank cell which is correct and moves as I enter text , I want to apply a CopyToRange:= to equal this result

but it reads the cell where this function is and not the result.

How do I make the macro accept the result.

Is there another way to do this.

Any help welcome, I am close but stalled......

Thanks

Chris

Hi Chris:

Let us have a look at ...

Book2
	A	B	C	D	E	F	G	H
1	Field1	Field2	Field3	Field4	Field5		AddressofcellaftertheLastrowinColumnA.	$A$4
2	1.1	1.1	1.1	1.1	1.1
3	1.2	1.2	1.2	1.2	1.2
4	1.3	1.3	1.3	1.3	1.3
5
Sheet7

Does it help?

 

[Chris S]

Re: Advanced Filter multiple sheets back to one, how can i d

Yogi

The plan was to pull in the data from each sheet each by running a macro with advanced filter.

then have the flexibility to just pull from one sheet . I will also link the 5 macros so that i can do all at once, one after the other automatically.

Once I can get the data to the master sheet then I have some more arranging but thats later.

So to get past this hurdle I would like to be able to write a variable into the macro that defines the cell range to copy to.

-with the result of the "address" displayed in a cell. how can i use this in the macro. its just text isnt it?

I will look at your example and see if it allows me to do something.

Let me know if you need more clarity.

Thanks for your time.

Chris

 

[Yogi Anand]

Re: Advanced Filter multiple sheets back to one, how can i d

Hi Chris:

Let us consider this simple list ...

Book2
	A	B	C	D	E	F	G	H	I
1	Field1	Field2	Field3	Field4	Field5		AddressofcellaftertheLastrowinColumnA.	A5	Field1
2	1.1	1.1	1.1	1.1	1.1
3	1.2	1.2	1.2	1.2	1.2
4	1.3	1.3	1.3	1.3	1.3
5
6
7
Sheet7

Now suppose I want to filter this using the criteria in range I1:I2, and for copying to I use the address computed and displayed in cell H1, then the following code ...

Sub y_chris1()
    yStr = Evaluate("=address(counta(a:a)+1,1,4)")
    Range("A1:E4").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
        "I1:I2"), CopyToRange:=Range(yStr), Unique:=False
End Sub

will filter the list and write the records extracted in cells H5:E8 ...

Book2
	A	B	C	D	E	F	G	H	I
1	Field1	Field2	Field3	Field4	Field5		AddressofcellaftertheLastrowinColumnA.	A9	Field1
2	1.1	1.1	1.1	1.1	1.1
3	1.2	1.2	1.2	1.2	1.2
4	1.3	1.3	1.3	1.3	1.3
5	Field1	Field2	Field3	Field4	Field5
6	1.1	1.1	1.1	1.1	1.1
7	1.2	1.2	1.2	1.2	1.2
8	1.3	1.3	1.3	1.3	1.3
9
Sheet7

I hope this helps!

 

[Chris S]

Re: Advanced Filter multiple sheets back to one, how can i d

Yogi

Absolutly fantastic... , I works like a charm.

Just for info I am filtering in excess of 65k lines over six sheets and ite realy fast.

Thanks so much, I can now get on with the rest of the manipulation.

CHEERS.

Chris........