Copy all values after autofilter

[Ammarbokhari]

Hi,
This has never happened to me before, or I have not noticed it, but today I noticed that once you auto filter data and then select the whole column/row and paste it into new sheets, formulas are replaced by values in the new sheet.
The way around this is either to select only the data range and not the whole column/row or to paste formats and then values one after other.
Is there any other way around? Is it an excel short coming or there is something wrong that I am doing?
I am trying to record a macro doing all these steps but I am stuck with this step as it only pastes values where as I need same sheet as the original with the exception of some values that are filtered.
This is the code (this is my first attempt to create a macro, so bear with me);

Sub Macro2()
    Application.ScreenUpdating = False
    Rows("3:3").Select 'Title row where autofilter is to be used
    Selection.AutoFilter
    ActiveSheet.Range("Y2").AutoFilter Field:=2, Criteria1:="=*" & Range("V4") & "*", Operator:=xlFilterValues 'Y2 contains the rangs of data and V4 is first criteria for autofilter
    ActiveSheet.Range("Y2").AutoFilter Field:=17, Criteria1:="=" & Range("W4") & "" 'Y2 is same and W4 is second criteria for autofilter
    ActiveSheet.Range("Y2").AutoFilter Field:=18, Criteria1:="=" & Range("X4") & "" 'Y2 is same as above X4 is third autofilter criteria
    Rows("3:3").Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveWindow.SmallScroll Down:=36
    Rows("1:843").Select
    Range("A843").Activate
    Selection.Copy
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Paste
    Columns("A:S").Select
    Range("A3").Activate
    Columns("A:S").EntireColumn.AutoFit
    Range("B3").Select
    Sheets("IBF-A (total qty)").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Rows("3:3").Select
    Application.ScreenUpdating = True
    End Sub

Looking forward to your response.
Thank you.

 

[Ammarbokhari]

A few more questions:
Is there a way to fit all three criterial into single line without repetition of ActiveSheet.range("Y2")
And also can I link column numbers (2,17,18) into cells V2,W2,X2 of the same source sheet? (in case the end user wants to filter some other columns not B, P and/or Q)