title: Need to add paste special condition a copy.destination for loop
body: <p>I have a range of data that is in the same position in every worksheet in a book and will always be in that position. When the macro is run the data should be copied and added to a report sheet. I have that part working but I need to use a paste special:</p>
  
  <pre><code>.PasteSpecial xlPasteValues
  </code></pre>
  
  <p>as there are formulas in the range. I am unsure where to add the paste special condition in this code, since I'm using <code>.Copy, Destination</code>.</p>
  
  <pre><code>Option Explicit
  Sub CreateTempPSDReport()
  
      Dim WS As Worksheet, Rept As Worksheet
  
      Set Rept = Sheets("Temporary PSD Report")
  
      Application.ScreenUpdating = False
  
      '--&gt; Loop through each worksheet except the report and
      '--&gt; Copy the set range to the report
      For Each WS In ThisWorkbook.Worksheets
          If Not WS.Name = "Temporary PSD Report" Then
              WS.Range("A42", "I42").Rows.Copy _
              Destination:=Rept.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
          End If
      Next
  
      Application.ScreenUpdating = True
  
  End Sub
  </code></pre>