Auto Filter one short list vs another very long list
 

I would like an example code of autofilter where on the linked sheet it will look up each part number in the yellow column and filter the pink column for that part number.

Then return the column G value, the column B "filter on" value and the column N dollar amount, as displayed in the blue return example.

Using the macro recorder I know the three columns have to be included in the filter and the copy the visible cells. The recorded code was to abstract for me to distill down to a proper autofilter macro.

Thanks,
Howard

https://www.dropbox.com/s/kaiky4skoe...test.xlsm?dl=0

Auto Filter one short list vs another very long list
 

Hi Howard,

Am Thu, 23 Apr 2015 03:56:15 -0700 (PDT) schrieb L. Howard:

https://www.dropbox.com/s/kaiky4skoe...test.xlsm?dl=0

please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Part Numbers"
You have to download it, because macros are disabled in OneDrive


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Auto Filter one short list vs another very long list
 

It just occurred to me that the link is a single sheet.

In reality the yellow list will be on one sheet and the column G, J & N will be on another sheet, in the same columns, G, J & N.

Not sure if autofilter is okay with two sheets.

The returns need to be on the sheet with the yellow list.

Hoard

Auto Filter one short list vs another very long list
 

Wow, never thought it would be that concise.

Thanks a ton, this will make a great how to example.

Appreciate it Claus.

Howard

Auto Filter one short list vs another very long list
 

Question, please.

I don't quite understand this portion, did you determine the range or was that part of the filter function that determined the A1:C54, with B & C empty?

CriteriaRange:=Sheets("Sheet2").Range("A1:C54")

It seems it is optional, did you put it on sheet 2 just as a reference?

Howard

Sub AdvFilter()
Dim LRow As Long

With Sheets("Parts")
LRow = .Cells(Rows.Count, 10).End(xlUp).Row

.Range("G1:N" & LRow).AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet2").Range("A1:C54"), _
CopyToRange:=Sheets("Sheet2").Range("F1:H1"), Unique:=False
End With
End Sub

Auto Filter one short list vs another very long list
 

Hi Howard,

Am Thu, 23 Apr 2015 04:50:23 -0700 (PDT) schrieb L. Howard:

I don't quite understand this portion, did you determine the range or was that part of the filter function that determined the A1:C54, with B & C empty?

CriteriaRange:=Sheets("Sheet2").Range("A1:C54")

the advanced filter demands a criteria range. And all ranges (List
range, criteria range and the destination range) must have the same
headers.

Therefore I copied the range to sheet2 and changed the headers to the
list header. You can also enter one ore more criterias to the empty
columns.
You can also change the headers in sheets "Part" and use that range as
criteria range.


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Auto Filter one short list vs another very long list
 

Hi Howard,

Am Thu, 23 Apr 2015 13:57:28 +0200 schrieb Claus Busch:

Therefore I copied the range to sheet2 and changed the headers to the
list header. You can also enter one ore more criterias to the empty
columns.

if you only want to filter by Part Number you can also change the
criteria range:
CriteriaRange:=Sheets("Sheet2").Range("A1:A54")


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Auto Filter one short list vs another very long list
 

Okay, I'll study that some more. I remove the CriteriaRange:=Sheets("Sheet2").Range("A1:A54") from the code and ran it, the F-H columns returned fine and the A B C was empty. Hmmm.

Are the required headers something I put on the sheet and the code will accept and copy to them?

I tried to move the F-H columns and the code failed, tried to edit the named range but no go.

How do I relocate the three column return values.

Nice and concise code, but it does have some rules to follow indeed.

Howard

Auto Filter one short list vs another very long list
 

Hi Howard,

Am Thu, 23 Apr 2015 05:21:14 -0700 (PDT) schrieb L. Howard:

Okay, I'll study that some more. I remove the CriteriaRange:=Sheets("Sheet2").Range("A1:A54") from the code and ran it, the F-H columns returned fine and the A B C was empty. Hmmm.

Are the required headers something I put on the sheet and the code will accept and copy to them?

important is the spelling of the headers. But you can rearrange them to
your expected cells.
Please have another look in OneDrive.
I changed the output columns to A, C and E on Sheet2


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Auto Filter one short list vs another very long list
 

I learned you move the headers on the sheet and match the code to the headers columns.

I'll look at the One Drive again.

H

Auto Filter one short list vs another very long list
 

Hi Claus,

I believe I get it now, the last code is a bit more basic and easier to understand.

That will make a good go to example for me.

Do appreciate the help and school housing.

Howard