title: Excel macro - paste only non empty cells from one sheet to another
body: <p>Below is the code which I am using to copy cells from one sheet and paste in to another.</p>
  
  <pre><code>Sheets("codes").Select
  Range("A5:A100").Select
  Selection.Copy
  Sheets("Sheet2").Select
  Range("B28").Select
  ActiveSheet.Paste
  </code></pre>
  
  <p>The problem with this is some cells in this range are blank but I do not want them to be copied to Sheet2. I have got some idea from <a href="https://stackoverflow.com/questions/821364/selecting-non-blank-cells-in-excel-with-vba">here</a> but this method is too long. Is there a way I can iterate on the selection and check if the value is non-empty and paste. This way I can also paste some other text (eg #NA) in the blank cells.</p>