vba excel 如何在没有字体/颜色/bg 颜色格式的情况下粘贴值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1610141/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
vba excel how to paste values without font/color/bg color formatting
提问by Kirk Hings
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 颜色进行特殊格式化，但是当粘贴到“摘要表”中时，它只需要粘贴值而不进行格式化。
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。
回答by Christian Payne
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
回答by Saikat
Once I selected the range then I put this
一旦我选择了范围，我就把这个
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
Works fine for me :)
对我来说很好用:)
回答by marg
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.
将 A1 替换为您的源，将 A2 替换为您的目标。
