title: How to prevent excel macro from pasting empty rows
body: <p>I am trying design a macro in excel 2007. Here is what I need it to do: </p>
  
  <p>When I enter an ID into a specific cell and run the macro, it will search for that ID in column A of a different workbook and autofilter. Then I need it to copy that data and paste it into the first workbook. My code is working, however when it is pasting a ton of extra rows beneath my data. How can I make it only copy and paste data and not empty rows? Here is my code:</p>
  
  <pre><code>Sub Medications()
      '
      ' Medications Macro
      '
      ' Keyboard Shortcut: Ctrl+m
      '
  
      Range("B1").Select
      Workbooks.Open Filename:= _
          "I:\Pharmacy\MTMP\2013\Master Lists\CMR Medication List.xlsx"
      Range("A1").Select
      ActiveCell.FormulaR1C1 = "Member ID"
      Range("A1").Select
      Selection.AutoFilter
      ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).AutoFilter Field:=1, Criteria1:=Workbooks("Standardized Format Spreadsheet.xlsm").Worksheets("Demographics").Range("B1").Value
      Cells.Select
      Selection.Copy
      Windows("Standardized Format Spreadsheet.xlsm").Activate
      Sheets("Detailed Medication List").Select
      Range("A1").Select
      ActiveSheet.Paste
      Windows("CMR Medication List.xlsx").Activate
      Application.CutCopyMode = False
      Application.DisplayAlerts = False
      ActiveWorkbook.Close
      Application.DisplayAlerts = True
      Sheets("Demographics").Select
  End Sub
  </code></pre>