VBA Excel仅将表格值复制到另一个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26946165/
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 Copy Table Values only to another sheet
提问by Amine Takieddine
I am trying to copy only the values from a table to a different sheet, however the below code is only copying the formulas thus losing references. I have tried different solutions such as adding .Value to .DataBodyRange.Copy Cells(i,1) however didn't work.
我试图仅将表中的值复制到不同的工作表，但是下面的代码只是复制公式，因此丢失了引用。我尝试了不同的解决方案，例如将 .Value 添加到 .DataBodyRange.Copy Cells(i,1) 但是没有用。
Thanks for your help
谢谢你的帮助
Sub loadData()
Application.AutoCorrect.AutoFillFormulasInLists = False
Dim wsh As Worksheet, i&
Application.ScreenUpdating = 0
With ActiveSheet.ListObjects(1)
If ActiveSheet.FilterMode Then .Range.AutoFilter
.DataBodyRange.Delete
i = 4
For Each wsh In ThisWorkbook.Worksheets
If wsh.Name <> "Template" Then
With wsh.ListObjects(4)
.DataBodyRange.Copy Cells(i, 1)
i = i + .ListRows.Count
End With
End If
Next wsh
.Range.AutoFilter Field:=5
End With
Application.ScreenUpdating = 1
End Sub `
回答by philippmuller
This questionmight help you. Try changing the format (values or formulas) when pasting the values using pastespecial.
这个问题可能对你有帮助。尝试使用 pastespecial 粘贴值时更改格式（值或公式）。
回答by peege
You are already using loops to accomplish your checks. You should use loops to finish the job and don't worry about using the clipboard to copy and paste. I can understand using copy and paste especially if you don't understand loops, but you clearly do.
您已经在使用循环来完成检查。您应该使用循环来完成工作，而不必担心使用剪贴板进行复制和粘贴。我可以理解使用复制和粘贴，特别是如果您不理解循环，但您显然理解。
You can do another loop where you set the values for the cells one at a time.
您可以执行另一个循环，一次为单元格设置一个值。
LastCol = Sheets("Sheet1").Cells(1, Columns.count).End(xlToLeft).Column 'Get last column of row with data
For col = 1 To LastCol
Sheets("Sheet2").Cells(i, col).Value = Sheets("Sheet1").Cells(i, col).Value
Next col
This isn't a complete solution, but if you insert this into your code where you are trying to copy and paste, you should get a good idea of how to complete this. Again, I'd avoid using copy and paste macros, simply because it's quicker to just write a three or four line loop in VBA than to record a macro, then edit it, clean it up, modify it, and debug it. It sure seems like less work to "record a macro" but in my experience, the code is quicker and more accurate. You know exactly what is happening instead of some parameters you might not want to question and don't know what they do.
这不是一个完整的解决方案，但是如果您将其插入到您尝试复制和粘贴的代码中，您应该很好地了解如何完成此操作。同样，我会避免使用复制和粘贴宏，因为在 VBA 中编写一个三行或四行循环比录制一个宏、然后对其进行编辑、清理、修改和调试更快。“录制宏”似乎工作量更少，但根据我的经验，代码更快、更准确。您确切地知道正在发生什么，而不是一些您可能不想质疑并且不知道它们做什么的参数。
