title: Copy data from one excel sheet to another (complex) using VBA based on column name
body: <p>I'm very new to VBA and after 5 hours of watching videos and Googling, I think this is just too over my head... any help is very much appreciated.</p>
  
  <p>So I have 2 excel worksheets: Sheet1 and Sheet2.  I have a Y/N column in Sheet1 and if the column = "Y" then I want to copy all the data from that row that has a matching column name in Sheet2.</p>
  
  <pre><code>Sheet1
  Product     Price     SalesPerson    Date    Commission     Y/N
    A          $25         John       1/9/15      $3           Y 
    B          $20         John       1/12/15     $2           N  
    B          $15         Brad       1/5/15      $1           Y
  
  Sheet2
  Price     Product     Date     Salesperson   
  </code></pre>
  
  <p>So for every time Y/N = Y then copy the data that matches over to sheet2 and do this until sheet1.col1 is null (looping).  The result would be this:</p>
  
  <pre><code>Sheet2
  Price     Product     Date     Salesperson
   $25         A       1/9/15        John
   $15         B       1/5/15        Brad
  </code></pre>
  
  <p>The columns are not in order and are far too numerous to manually input. Then last but not least the Y/N column would need to clear upon finish.  I have tried to alter this with no luck:    </p>
  
  <pre><code>Sub CopyHeaders()
  Dim header As Range, headers As Range
  Set headers = Worksheets("Sheet1").Range("A1:Z1")
  
  For Each header In headers
      If GetHeaderColumn(header.Value) &gt; 0 Then
          Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("Sheet2").Cells(2, GetHeaderColumn(header.Value)).End(xlDown).Offset(1, 0)
      End If
  Next
  End Sub
  
  Function GetHeaderColumn(header As String) As Integer
  Dim headers As Range
  Set headers = Worksheets("Sheet2").Range("A1:Z1")
  GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
  End Function
  </code></pre>
  
  <p>This was designed to do something different than what I'm trying to do and I don't think I'm capable of changing this to work for me. How wold I do this?</p>