Filter Workbook1 column A with a list on Workbook2 column A (In the background preferably)

[bino1121]

Workbook1 column A is a list of hundreds of building numbers. I only need certain buildings at a given time not all of them. Workbook2 (column A) is a template sheet that has a list of building numbers that is constantly being updated or changing. I need to filter column A in workbook1 by a list that shows up on column A in Workbook2. I currently have it running in the VBA Code where I had to enter each specific building number "building 1", "building 2", ...., Etc. Problem is the list of building numbers changes from time to time so I can't keep going into the VBA code and editing the building numbers individually I would rather Call a master list that is updated to be the driving force behind the filter.

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

In this case, both files.

[bino1121]

DataSet.xlsxUpdatedSheet.xlsx

DataSet is building numbers and Updated sheet workbook is the other workbook. I don't have the VBA code for it because I've been filtering the individual sheet using record macro.

[Mumps1]

It would help if you could attach both workbooks and let us know in which workbook you want to place the macro.

[bino1121]

I attached them in a separate comment cause someone else said the same thing. The macro/filter should be applied to the Dataset workbook and then the UpdateSheet is the criteria for the filter (needs to be two separate workbooks because one sheet is a template and the other sheet is a list of buildings that is constantly being update.

[beyond Excel]

I attached them in a separate comment cause someone else said the same thing. The macro/filter should be applied to the Dataset workbook and then the UpdateSheet is the criteria for the filter (needs to be two separate workbooks because one sheet is a template and the other sheet is a list of buildings that is constantly being update.

Hi bino. The following model:

Example_1.xlsm
\_______________________________/

apply an advanced filter between two sheets of the same workbook (I did it this way because you didn't upload anything useful to the Forum).

But you will see that to adapt it to your needs you just have to change:

- Workbook objects,
- Worksheet objects, and
- The respective cell ranges.

I suggest you try to adapt this to what you have and then comment:

Sub Macro8() 
Dim criteria_Range As Range 
Rem --------------------------------> 
Set criteria_Range = ActiveWorkbook.Sheets("Sheet2").Cells(1).CurrentRegion.Columns(1) 
Rem --------------------------------> 
With ActiveWorkbook.Sheets("Sheet1") 
  .Cells(1).AutoFilter 
  .Cells(1).CurrentRegion.AdvancedFilter 1, criteria_Range, Empty, False 
End With 
Rem --------------------------------> 
End Sub  

[bino1121]

Ok so here is the code I'm using so far to use advanced filter.... How do I change it so the range is control + shift+ down so the macro always selects everything in that column even if the column size is +- 12737?? If the code looks a little odd that's because it was a picture that I used copy and paste from Iphone so overall there's no errors in the actual code in VBA I just need to know how to implement the range as control + shift + Down in the existing code.

Sub Filter_Test ()

Range ("A3 :Al2737") .AdvancedFilter Action: =xlFilterInPlace, CriteriaRange:= _

Sheets ("FACILITIES"). Range ("Al:A97"), Unique:=False

End Sub

[beyond Excel]

Sub Filter_Test() 
Range("A3", Range("A3").End(xlDown)).AdvancedFilter Action:=xlFilterInPlace, _ 
  CriteriaRange:=Sheets("FACILITIES").Range("Al:A97"), Unique:=False 
End Sub  

[bino1121]

How would this work with the range being the same for the facilities sheet?? I tried doing it the same way as the "A3" one you did above and it didn't work.

[beyond Excel]

How would this work with the range being the same for the facilities sheet?? I tried doing it the same way as the "A3" one you did above and it didn't work.

This VBA code is designed to work on the active sheet: did you adapt the code to work on that other sheet?...

[bino1121]

Yea so I changed the range from your code (A1:A97) I need that range to also be Control shift down instead of that set range. The first part of the code runs good it selects the correct column from the first page as its supposed to. I just need both ranges to be a variable size range as opposed to a defined value of ranges.

[beyond Excel]

Look at post #6.