title: vba excel how to paste values without font/color/bg color formatting
body: <p>I've got a macro to copy a summary row from each of a series of worksheets. The summary row is specially formatted with font/font color/bg color, but when pasted into the 'sumamry sheet', it needs to just paste values without formatting. </p>
  
  <pre><code>For LoopIndex = StartIndex To EndIndex
      ' start in a task sheet
      Sheets(LoopIndex).Select
      CopiedCells = ActiveSheet.Range("A156:L156").Copy
  
      ' now move to Summary sheet
      Sheets("Summary Sheet").Select
      ActiveSheet.Range("A8").Select
      ActiveCell.EntireRow.Insert
  
      ActiveCell.PasteSpecial Paste:=xlPasteValues
      ' tried variations of: ActiveCell.PasteSpecial paste:=xlValues, operation:=xlPasteSpecialOperationNone
  
      Application.CutCopyMode = False ' clears clipboard
  Next LoopIndex
  </code></pre>
  
  <p>All the research I've done says the PastSpecial, xlValues, xlPasteValues should work but nothing strips the formatting, don't know what I'm doing wrong here. It does paste the values rather than the referenced values, so that is good. I have a macro to reset the formatting in loop but I'd like to make more efficient. I'm using Excel 2007.</p>