VBA 代码 - 保留复制到不同工作表的数据的格式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15438654/
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 Code - Retain Formatting for data copied to different sheet
提问by JohnM
As a new user of VBA in Excel I'm trying to complete the following:
1) Check multiple rows of one column in a sheet called "Index" for a condition (My condition is to look for the letter Y in cells in Column J)
2) Copy selected Columns from each row where the condition is satisfied to another sheet as programmed in the VBA Code, e.g. "Sheet2"
3) Retain Formatting of cells that are copied, i.e. Formulas and Bold Font in particular.
作为 Excel 中 VBA 的新用户，我正在尝试完成以下操作：
1) 检查名为“索引”的工作表中一列的多行以查找条件（我的条件是在 J 列的单元格中查找字母 Y )
2) 将条件满足的每一行中的选定列复制到 VBA 代码中编程的另一个工作表中，例如“Sheet2”
3) 保留复制的单元格的格式，特别是公式和粗体字体。
I asked a question previously at VBA code to copy selected columns from rows that meet a condition to another sheetand was kindly given the following code by ATl LED that works and copies the values but not the formatting (I forgot to include this in my question)
我之前在VBA 代码中问过一个问题，将满足条件的行中的选定列复制到另一张工作表中，并且 ATl LED 亲切地给出了以下代码，该代码可以工作并复制值但不复制格式（我忘了在我的问题中包括这个)
I experimented with the code and tweaked .Value to .FormulaR1C1 in the IF Statement. The formulas are copied and work a treat in the sheet copied to, i.e. Sheet2 but I can't get the formatting to work.
我对代码进行了试验，并将 IF 语句中的 .Value 调整为 .FormulaR1C1。公式被复制并在复制到的工作表中工作，即 Sheet2，但我无法使格式工作。
What am I missing?
我错过了什么？
Sub try3()
Dim i, x As Long
Dim Y as String
Dim ws1 As Worksheet: Set ws1 = ActiveWorkbook.Sheets("Index")
Dim ws2 As Worksheet: Set ws2 = ActiveWorkbook.Sheets("Sheet2")
x = 5
Y = "Y"
For i = 2 To 500:
If ws1.Cells(i, 10) = Y Then
Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value
x = x + 1
End If
Next i
End Sub
Thanks, JohnM
谢谢，约翰
采纳答案by Kazimierz Jawor
Place the following line of code:
放置以下代码行：
Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Copy Range(ws2.Cells(x, 1), ws2.Cells(x, 7))
instead of your line:
而不是你的线路：
Range(ws2.Cells(x, 1), ws2.Cells(x, 7)).Value = Range(ws1.Cells(i, 3), ws1.Cells(i, 9)).Value
Or keep both in the same order if in ws1 sheet are any formulas which you don't want to copy (but values).
或者，如果在 ws1 表中有任何您不想复制的公式（但值），则将两者保持相同的顺序。
