title: Excel copy columns based on a condition
body: <p>Hi I have a spreadsheet which I need to extract some data and copy it to a new sheet. </p>
  
  <p>Below is an image of the spreadsheet. What I want is a copy of all the yellow highlighted columns in a new sheet when column M &amp; Column N contain numbers (in this case there are only 2 that will be on the second sheet- row 7 &amp; row 26). This sheet is quite big so this is just a sample.</p>
  
  <p><img src="https://i.stack.imgur.com/C6ASs.png" alt="enter image description here"></p>
  
  <pre><code>Here is the code I have tried to use:
  
      Sub CopyYesAdrian()
  Dim a As Variant, o As Variant
  Dim i As Long, j As Long, n As Long
  Application.ScreenUpdating = False
  With Sheets("Sheet1")
    a = .Cells(1, 1).CurrentRegion
    n = Application.CountIf(.Columns(9), "Yes")
    ReDim o(1 To n + 1, 1 To 11)
  
  End With
  j = j + 1: o(j, 1) = "CORP ID": o(j, 2) = "Application ID"
  o(j, 3) = "Orig - Country": o(j, 4) = "Network DDI": o(j, 5) = "PTT ACCESS": 
    o(j, 6) = "TERM - COUNTRY": o(j, 7) = "TERM SW/TRUNK"
  : o(j, 8) = "TERM DIGITS": o(j, 9) = "PSTN ROUTE": o(j, 10) = "PLAN": o(j,  
  11) = "DNIS"
  For i = 2 To UBound(a, 1)
  If IsError(a(i, 6)) Then
  
   ElseIf a(i, 8) = "Yes" And a(i, 6) Like "[0-9][0-9][0-9][/][0-9]*" Then
      j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 3)
      o(j, 3) = a(i, 4): o(j, 4) = a(i, 6): o(j, 5) = a(i, 8):  o(j, 6) = a(i,   
  9):  o(j, 7) = a(i, 10):  o(j, 8) = a(i, 11)
  :   o(j, 9) = a(i, 12):   o(j, 10) = a(i, 12):   o(j, 11) = a(i, 12)
    End If
  Next i
  With Sheets("Sheet2")
    .UsedRange.Clear
    .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
    .UsedRange.Columns.AutoFit
    .Activate
  End With
  Application.ScreenUpdating = True
  End Sub
  </code></pre>