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

Question

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.

Christian Payne · Accepted Answer · 2009-10-23 03:04:14Z

6

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

answered Oct 23, 2009 at 3:04

Christian Payne

7,1695 gold badges42 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TylerH Over a year ago

How can you insert something before you've copied it?

marg · Accepted Answer · 2009-10-22 21:56:18Z

4

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.

answered Oct 22, 2009 at 21:56

marg

2,8251 gold badge31 silver badges34 bronze badges

2 Comments

Stay-at-home-dad Over a year ago

I know, this should work huh? But nope, it doesn't. Maybe there's a weird setting in the workbook I got from the client. I can paste manually without formatting, just not via vba code. Any other suggestions?

marg Over a year ago

The only explanation I have is that the sheet is not active. Try replacing ActiveSheet with the Tablename.

Saikat · Accepted Answer · 2013-07-12 07:42:58Z

4

Once I selected the range then I put this

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Works fine for me :)

answered Jul 12, 2013 at 7:42

Saikat

17.5k22 gold badges127 silver badges146 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related