vba excel how to paste values without font/color/bg color formatting

Question

Got a macro to copy a summary row from each of a series of worksheets. Summary row is specially formatted with font/font color/bg color, but when pasted into the 'sumamry sheet' needs to just paste values without formatting.

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

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. Using Excel 2007.

Thanks in advance for your help.

Answer 1

ActiveSheet.Range("A1").EntireRow.Copy
ActiveSheet.Range("A2").EntireRow.PasteSpecial xlPasteValues
Application.CutCopyMode = False

or

ActiveSheet.Range("A2").EntireRow.Value = ActiveSheet.Range("A1").EntireRow.Value

Replace A1 with your source and A2 with your target.

Answer 2

That's really odd!

The reason is that you are Copying, Inserting and then Pasting. Try Insert, Copy and then Paste:

'we must commence on the Summary Sheet
Sheets("Summary Sheet").Select
For LoopIndex = StartIndex To EndIndex

    ' insert the row before we start
    ActiveSheet.Range("A8").Select
    ActiveCell.EntireRow.Insert

    ' select the task sheet
    Sheets(LoopIndex).Select
    CopiedCells = ActiveSheet.Range("A156:L156").Copy

    ' now move to Summary sheet
    Sheets("Summary Sheet").Select

    ActiveCell.PasteSpecial Paste:=xlPasteValues
    ' tried variations of: ActiveCell.PasteSpecial paste:=xlValues, operation:=xlPasteSpecialOperationNone

    Application.CutCopyMode = False ' clears clipboard
Next LoopIndex

For what it's worth, I've had problems using copy & paste. It means that while your macro is running, you can't do much else.

Since it is a fixed range, I would suggest this:

For LoopIndex = StartIndex To EndIndex
    Sheets("Summary Sheet").Range("A8").EntireRow.Insert
    For i = 1 To 12
        Sheets("Summary Sheet").Cells(8, i) = Sheets(LoopIndex).Cells(156, i)
    Next
Next