title: Copying data from master sheet to workbooks based on criteria
body: <p>The below code struggles at the final "paste section". It opens up the new spreadsheet which I want to paste in but instead pastes in the underlying sheet where the data already exists. Any ideas on how to get this into the new sheet? </p>
  
  <pre><code>Option Explicit
  
  Sub newfiles()  
      Dim personname As Variant
      Dim workbookname As Variant
      Dim namerange As Integer
      Dim i As Integer
      Dim personame As Variant
      Dim k As Integer
      Dim l As Integer
  
      k = Range("A10000").End(xlUp).Row
  
      Range("N3:N" &amp; k).Copy
  
      With Range("XFC1:XFC" &amp; k)
          .PasteSpecial xlPasteAll
          .RemoveDuplicates Columns:=1, Header:=xlNo
      End With
  
      namerange = Range("XFC10000").End(xlUp).Row        
  
      For i = 1 To namerange
          personname = Range("XFC" &amp; i).Value
          Workbooks.Add 
          workbookname = ActiveWorkbook.Name
  
          Windows("Test 1.xlsm").Activate
          Sheets("Sheet1").Select
  
          Cells.Copy
          Range("A1").Select
          Windows(workbookname).Activate
          Sheets("Sheet1").Select
  
          With Cells
              .PasteSpecial xlPasteAll
              .PasteSpecial xlPasteColumnWidths
          End With
  </code></pre>