title: VBA Run-time error '1004': Paste method of Worksheet class failed
body: <p>At first this code was "sort of working", meaning that it would paste the information to the destination but not in the correct cells, and adding additional rows instead of skipping empty rows. Once I got it to paste correctly to the appropriate cells it no longer wants to work.  </p>
  
  <p>I'm sure this a fairly simple issue but I'm at a loss.  Any help would be greatly appreciated </p>
  
  <pre><code>Private Sub CommandButton1_Click()
  
  Dim Myfile As String
  Dim erowFilepath = ("C:\Users\YOU DONT NEED TO SEE THIS PART\Desktop\LINKED TRACKERS\")
  Myfile = Dir("C:\Users\OR THIS PART\Desktop\LINKED TRACKERS\")
  
  Do While Len(Myfile) &gt; 0
      If Myfile = "COMPANY_CYCLE.xlsm" Then
          Exit Sub
      End If
  
      Workbooks.Open (Filepath &amp; Myfile)
      Worksheets("ROSTER").Range("A3:O3").Copy
      ActiveWorkbook.Save
      ActiveWorkbook.Close
  
      erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      ' ***The line below is the line I get an error***
      ActiveSheet.Paste Destination:=Worksheets("MANNINGROSTER").Range(Cells(erow, 1), Cells(erow, 1))
  
      Myfile = Dir
  Loop
  
  End Sub  
  </code></pre>
  
  <p>Long story short: I'm attempting to consolidate data from 4 different workbooks that users will input data, to a single workbook that I can monitor and process information as it is put in day by day.</p>
  
  <p>I'm fairly certain that the issue is stemming from the source worksheets being password protected and that the paste destination is the same for all 4 workbooks as well.</p>
  
  <p>I can,t seem to figure out a way to correct this.  One error leads to another, and another, etc.</p>