Dynamic Ranges for Filter Macros

[steveh8204]

Hi,

I've created several different Macros for Filtering a spreadsheet for defined values (SKUs) but occasionally these values change. I was wondering is possible to create a macro in VBA that filters a spreadsheet based on all the values in a column on a different sheet in a different file?


I could then change the values in the relevant column as opposed to delving into the code each time to change/add/delete them.


Other people maintain the spreadsheet too so asking them to delve into VBA code isn't going to work but just updating a list will.

Thanks in advance.

 

[Fluff]

You could do something like

Sub steveh8204()
   Dim Ary As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary), xlFilterValues
   End With
End Sub

 

[steveh8204]

You could do something like

Sub steveh8204()
   Dim Ary As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary), xlFilterValues
   End With
End Sub

Nice one, works perfect, thank you!

Don't suppose you know how to make it work on 2 filters within the table?

 

[Fluff]

Something like

Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

 

[steveh8204]

Nice one, thank you very much, and for the quick reply!

 

[Fluff]

You're welcome & thanks for the feedback

 

[steveh8204]

Something like

Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With Workbooks("Book2.xlsm").Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ThisWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

Is it possible to create the lists for these macros in PERSONAL.XLSB? I was hoping to use this Macro without having a seperate spreadsheet open at the same time (I always have enough Windows open as it is in work) so was hoping to use PERSONAL.XLSB as its always open, well at least for Macros it is anyway. I tried opening the file but it doesnt open. Can it only be used for Macros?

I cant store the lists in the files I use to filter as they are company wide documents that are controlled from afar so its not really feasible.

Thanks in advance.

 

[Fluff]

Do you have the code in your Personal.xlsb file?

 

[steveh8204]

Do you have the code in your Personal.xlsb file?

I can get the Macro code in there easy enough. Its just opening the PERSONAL.XLSB sheet to put the varaibles in that the filter needs to work on.

 

[Fluff]

If the code is in your Personal.xlsb file try

Sub steveh8204()
   Dim Ary1 As Variant, Ary2 As Variant
   
   With ThisWorkbook.Sheets("List")
      Ary1 = .Range("A1", .Range("A" & Rows.Count).End(xlUp)).Value2
      Ary2 = .Range("B1", .Range("B" & Rows.Count).End(xlUp)).Value2
   End With
   With ActiveWorkbook.Sheets("sheet1")
      .Range("A1:Z1").AutoFilter 3, Application.Transpose(Ary1), xlFilterValues
      .Range("A1:Z1").AutoFilter 5, Application.Transpose(Ary2), xlFilterValues
   End With
End Sub

The workbook you want to filter needs to be the active workbook.