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

excel, paste, vba

Solution

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

Problem

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. ``` 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. I'm using Excel 2007.

Original source