title: Set filter in Excel with VBA
body: <p>The below macro will allow me to find a name in the heading in sheet 1 and copy the entire column to sheet 2. Now I want to continue the code, but am facing a problem, which I will try explain.</p>
  <pre><code>Sub CopyColumnByTitle()
  'Find &quot;Name&quot; in Row 1
    With Sheets(1).Rows(1)
     Set t = .Find(&quot;Name&quot;, lookat:=xlpart)
  'If found, copy the column to Sheet 2, Column A
  'If not found, present a message
       If Not t Is Nothing Then
          Columns(t.Column).EntireColumn.Copy _
            Destination:=Sheets(2).Range(&quot;A1&quot;)
         Else: MsgBox &quot;Title Not Found&quot;
       End If
    End With
  End Sub
  </code></pre>
  <p>after all data is pasted in the sheet 2 as below ....</p>
  <pre><code>Sheet 2
  Name Age Address    Date of Birth
  John    25  US  1-Sep-11
  Hary    26  US  1-Sep-11
  John    27  UK  1-Sep-11
  Hary    28  US  2-Sep-11
  King    29  UK  3-Sep-11
  Peter   30  US  3-Sep-11
  </code></pre>
  <p>I need to set filters as shown below and copy the filtered data to sheet 3 as above code does:</p>
  <ol>
  <li>I need to set filter criteria on sheet 2 which helps me to see <code>Name</code>s that are equal to &quot;John&quot; or &quot;Hary&quot; and copy and paste the entire data into sheet 3.</li>
  <li>I need to set another filter where <code>Name</code> is equal to &quot;John&quot; and <code>Date of Birth</code> is equal to &quot;1-Sep-11&quot; (note the date should always be yesterday). Copy and paste the entire
  data into sheet 4.</li>
  <li>On the third time, I need to set a filter where <code>Name</code> is equal to &quot;King&quot; and copy the and past the entire data into sheet 5.</li>
  </ol>
  <h2>Update</h2>
  <p>Thanks a lot John for your reply, the reply you gave is effective but I have already designed my code.</p>
  <p>I am pasting a part of code as it is not possible to paste the entire code.</p>
  <p>The code allows me to copy data from one workbook to another, but while copying data, I need to copy an entire column because there are some blank cells in it. So if I don't use <code>.EntireColumn</code>, the macro does not copy cells after the blank cell. Also now, while pasting the data into the other work book, I need to paste it without the heading.</p>
  <pre><code>Windows(&quot;macro2.xlsm&quot;).Activate
  Range(Range(&quot;M2&quot;), Range(&quot;N2&quot;).End(xlDown)).EntireColumn.Select
  Application.CutCopyMode = False
  Selection.Copy
  Windows(&quot;formula.xls&quot;).Activate
  Range(Range(&quot;I2&quot;), Range(&quot;J2&quot;).End(xlDown)).EntireColumn.Select
  Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
  xlNone, SkipBlanks:=False, Transpose:=False
  </code></pre>