Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter a list with data from another sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter a list with data from another sheet
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 wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter a list with data from another sheet
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 Petebull wrote: 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 escreveu: 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 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down list to run Auto filter on another sheet | Excel Discussion (Misc queries) | |||
how do I filter a list of items based on values in another sheet | Excel Discussion (Misc queries) | |||
Filter Range on Sheet B Based on List on Sheet A | Excel Discussion (Misc queries) | |||
Advanced Filter w/ multiple sheet List Range | Excel Worksheet Functions | |||
Can you filter sheet of data & on another sheet make four groups under each other? | Excel Programming |