Filter a list with data from another sheet

[Petebull]

Hello

I have a excel sheet were i need to filter some information using
values on other sheet, and put the results, filtered in another sheet.

I will try to put it in other words.

The macro should use values in the column b on sheet2 to filter the
table on sheet1 that as a column with the same values of course, and
copy it to sheet three.

What i'm trying to see on sheet3 is what lines in sheet1 correspond to
the sheet two criteria collumn.

Thanks a lot in advance

 

[james.billy]

Well you could set a loop up and just apply the filter copy to sheet3
and move on, some thing like:

dim xCell as range
dim xRng as range
set xRng = Range("B2:B" & Cells(65536,2).end(xlup).row) ' Goes from B2
to the last cell --containing data in column B
for each xCell in xRng
Sheets("Sheet1").select
Range("A1:M1000").autofilter, 4, xCell 'Assumes Sheet1 Range is A1 to
M1000
Range("A2:M1000").copy Sheet3.Range("A1").end(xldown)
Sheets("Sheet2").select
next xCell

An easier and less involved way might be to do a vlookup on sheet1 to
see which of the criteria in sheet2 column B are on sheet 1 and then do
an autofilter on this new column.

All you would need to do is do something like =Vlookup(Sheet1Value,
Sheet2!B1:B1000, 1, false) this would then return the criteria from
sheet2 column B if it existed or give #N/A if it didn't then apply the
filter for does not equal #N/A.

James

 

[Petebull]

Thanks

It works, it filters the list using the criterias and it should copy
the data from each time it filters to the new sheet, but, the thing is
that it gives me an error:
Run time error '1004'
the copy and paste areas are diferent size and shape.

Thanks a lot

(e-mail address removed) escreveu:

 

[james.billy]

Hi,

Replace this line:

Range("A2:M1000").copy Sheet3.Range("A1").end(xldown)

With

Range("A2:M1000").copy
Sheets("Sheet3").select
Range("A" & Cells(65536,1).end(xlup).row + 1).pastespecial xlpasteall

This will switch to Sheet 3 then goto the bottom of the sheet and back
up to the last cell that was used and pastespecial all of the data.

To answer the error, the easiest way around this is to select only one
cell and paste there as the error is saying that the amount of columns
/ rows does not match the copied range.

James