Filter data to another sheet with VBA calculation

[mattadams84]

I have set up a macro to filter through a sheet and to copy across rows that meet certain criteria to another sheet. Here is the marcro for that:


Sub O15Filter75()
Application.ScreenUpdating = False
Sheets("O1.5 (Filter 2)").Range("A5:AO" & Sheets("O1.5 (Filter 2)").Range("A" & Rows.Count).End(xlUp).Row).ClearContents
With Sheets("O1.5").Range("A4:AO" & Sheets("O1.5").Range("A" & Rows.Count).End(xlUp).Row)
.AutoFilter 8, ">=" & Sheets("FILTERS").Range("D7")
.AutoFilter 9, ">=" & Sheets("FILTERS").Range("D8")
.AutoFilter 10, ">=" & Sheets("FILTERS").Range("D8")
.AutoFilter 11, ">=" & Sheets("FILTERS").Range("D9")
.AutoFilter 12, ">=" & Sheets("FILTERS").Range("D10")
.AutoFilter 13, ">=" & Sheets("FILTERS").Range("D11")
.AutoFilter 14, ">=" & Sheets("FILTERS").Range("D12")
.Offset(1).Resize(.Rows.Count - 1, .Columns.Count).SpecialCells(12).Copy Sheets("O1.5 (Filter 2)").Cells(5, "A")
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub

I now want to add a different filter in to that code that would need to calculate something. I want to create a filter that works out if the value in a row in column S is in between the values of column T and U. This is a calculation that i am not performing within the excel sheet, it is something i would like to do in VBA, however i cant work out how to do it using the autofilter because there is technically no columun with that data in to filter.

This is the formula that i would use if i had a column in the excel sheet:


=IF(Sxx=MEDIAN(Sxx,Txx,Uxx),TRUE,FALSE)

If anyone can help me integrate that in to my VBA code i ould be grateful.

[Kenneth Hobs]

Welcome to the forum!

Make your code add a column with the formula, add the filter, then hide the column, do the paste, then delete the column. It might be safer to copy the workbook and then make the code do its thing there and then delete that scratch workbook.