title: VBA - having trouble copying data after autofilter
body: <p>I am trying to write a VBA code that will filter a report in sheet 'New Business Data' based on Column A. Then, it should paste the results from Column B to column K in Sheet 'New Sales'
  However, only part of the data is coming through because there may be some empty cells in columns C-J. I know the issue is with the Range(Selection...) but not sure how to correct</p>
  
  <pre><code>'Stage 6
  Sheets("New Sales").Select
  Range("B1").End(xlDown).Offset(1, 0).Select
  ActiveCell.FormulaR1C1 = "Accounts Live - Stage 6"
  
  Sheets("New Business Data").Select
  ActiveSheet.Range("$A$1:$K$122").AutoFilter Field:=1, Criteria1:= _
      "Stage 6 - Live"
  Range(Selection, Selection.End(xlToRight)).Select
  Range(Selection, Selection.End(xlDown)).Select
  Selection.Copy
  Sheets("New Sales").Select
  Range("B1").End(xlDown).Offset(1, 0).Select
  ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  
  Range("B1").End(xlDown).Offset(1, 0).Select
  ActiveCell.FormulaR1C1 = "Total"
  </code></pre>