Excel Autofilter Copy and Paste with Changing Values
 

I have used a macro written by Ron de Bruin entitled €œCopy5€� that uses
Autofilter to find, copy, and paste rows from one worksheet to another.

My search criteria are called out as follows:

My_Range.AutoFilter Field:=2, Criteria1:="=" &
Worksheets("Calculations").Range("A16").Value
My_Range.AutoFilter Field:=3, Criteria1:="=" &
Worksheets("InputPage").Range("A13").Value
My_Range.AutoFilter Field:=4, Criteria1:="=" &
Worksheets("InputPage").Range("B2").Value
My_Range.AutoFilter Field:=9, Criteria1:="=" &
Worksheets("InputPage").Range("C14").Value

This is my paste information, in this case:
Set My_Range = Worksheets("Options").Range("A1:O" &
LastRow(Worksheets("Options")))
My_Range.Parent.Select
DestSh = Worksheets("InputPage")

With My_Range.Parent.AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 10).Resize(.Rows.count - 1, 5) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then
rng.Copy
With DestSh.Range("D14")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
Application.CutCopyMode = False
End With
End If
End With

The single-cell ranges for the first three criteria are always static.

I have two questions, as follows:
1) The 4th listed Criteria for Field9 refers to cell C14 on the input page.
The final result pastes into Row 14 of that page. Every value for every row
in Column C is different for the extent of the worksheet, and each of them
needs to be filtered by this subroutine. I am currently running this sub
separately for EVERY row on the worksheet (which is quite long) and having
each sub call out the next. This is cumbersome, as the filter switches
repeatedly between the €œOptions€� page and the €œInputPage€�. There must be a
way to ask the filter to look at the value in the C column of each row and
paste the result in the corresponding row starting in the D column in a
specified range of rows, so that I can run one filter to do the whole job,
instead of running a separate sub for each line. HELP!!!!
2) Far less important: Id like, for instance, for the 3rd listed Criteria
for Field 4 to match to what is in B2 of the €œInputPage€�, but, if the
corresponding value in Field 4 is blank, Id like it to still take that row.
How do I do this without running a second subroutine? Maybe an €œOR€� in there
somewhere?

Please, I beg of you.