vba excel如何粘贴没有font / color / bg颜色格式的值

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.

我有一个宏来复制一系列工作表中的每一个摘要行。摘要行使用字体/字体颜色/ bg颜色进行特殊格式化,但是当粘贴到“sumamry工作表”时,它只需粘贴值而不进行格式化。

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.

我所做的所有研究都说过PastSpecial,xlValues,xlPasteValues应该可以工作,但没有什么可以去除格式化,不知道我在这里做错了什么。它会粘贴值而不是引用的值,因此这很好。我有一个宏来重置循环中的格式,但我想提高效率。我正在使用Excel 2007。

3 个解决方案

#1

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

#2

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

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

Replace A1 with your source and A2 with your target.

将A1替换为源,将A2替换为目标。

#3

Once I selected the range then I put this

一旦我选择了范围,我就把它放了

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

Works fine for me :)

对我来说工作正常:)

#1