title: VBA Copy Destination but as values
body: <p>I'm trying to transfer data from one document to a different document using "Copy Destination" as I want to avoid using the clipboard but I want it to stop taking the formatting with it...</p>
  
  <pre><code>Dim Sheet As Worksheet
  Dim FoundLocationSheet As Boolean
  Dim n As Long
  Dim AllSheet As Worksheet
  Set AllSheet = Sheets("Main")
  
  'Transfer data
  For n = 1 To AllSheet.Cells(Rows.Count, 1).End(xlUp).Row
      If AllSheet.Cells(n, 1) = "TiTle" Then
          With Sheets(AllSheet.Cells(n - 1, 1).Value)
              AllSheet.Cells(n, 1).CurrentRegion.Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)
          End With
      End If
  Next n
  </code></pre>
  
  <p>The macro could potentially pull data from <code>A20:L40</code> and put it into <code>A15:L35</code>...</p>
  
  <p>I'd been trying lots of different things with <code>AllSheet.Cells(n, 1).CurrentRegion.Copy Destination:=.Cells(Rows.Count, 1).End(xlUp).Offset(2, 0)</code> but can't work out how to make it work...</p>
  
  <p>The size of the sheet means clearing formats takes way too long :/</p>
  
  <p>Any ideas?</p>