title: Copy all cells with certain value into another column skipping blanks
body: <p>I have three columns, A, B and C:<br>
  Column A contains names, NAME1, NAME2, etc.<br>
  Column B contains only the values "YES" or "NO".<br>
  Column C is suppose to contain the names from column A that have value "YES" in column B. </p>
  
  <p>I can say that as long as the value is "YES" in column B, copy the value from column A to column C. Very simple with: </p>
  
  <pre><code>C1=IF(B1="YES",A1,"")
  </code></pre>
  
  <p>But this will include blank cells, which I don't want to. So I guess I am looking for a way to copy all the names from column A with value "YES" in column B and paste them into column C skipping the blanks. </p>
  
  <p>I did find a VBA project that colors all the cells within a column with a certain value. I am not sure how to edit this into what I need. Here is the code I came up with so far. </p>
  
  <p><strong>ISSUES</strong><br>
  1) <strike>Runtime Error '1004' Application-defined or Object-defined error</strike><br>
  2) <strike>Copying from Column A</strike><br>
  3) <strike>Check and Remove Duplicates from NewRange</strike>  </p>
  
  <p><strong>EDIT 1</strong>: Added comment rows into the code<br>
  <strong>EDIT 2</strong>: Change NewRange to be made from column A with Offset (untested due to runtime error)<br>
  <strong>EDIT 3</strong>: Code for copying form one sheet separated from code for pasting into another sheet<br>
  <strong>EDIT 4</strong>: Added correction from user @abahgat<br>
  <strong>EDIT 5</strong>: Remove duplicates</p>
  
  <pre><code>Sub RangeCopyPaste()
  Dim cell As Range
  Dim NewRange As Range
  Dim MyCount As Long
  MyCount = 1
  
  '--&gt; Loop through each cell in column B
  '--&gt; Add each cell in column A with value "YES" in column B to NewRange 
  For Each cell In Worksheets("Sheet1").Range("B1:B30")
      If cell.Value = "YES" Then
          If MyCount = 1 Then Set NewRange = cell.Offset(0,-1)
          Set NewRange = Application.Union(NewRange, cell.Offset(0,-1))
          MyCount = MyCount + 1
      End If
  Next cell
  
  '--&gt; Copy NewRange from inactive sheet into active sheet
  NewRange.Copy Destination:=activesheet.Range("C1")
  
  '--&gt; Remove Duplicates
  activesheet.Range("C1:C30").RemoveDuplicates
  
  End Sub
  </code></pre>