Excel VBA：将单元格从多个工作表复制到单个工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13588637/
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
Excel VBA: Copy cells from multiple sheets to a single sheet
提问by wasabigeek
I am pretty new to VBA and am trying to automate a process at work where I need to extract select cells from an array of 6 sheets and consolidate them in another sheet. The code I have works, but is kinda "clunky" - I am using the excel copy and paste functions, but can't seem to find a good solution away from the copy-and-paste function. And when I try to add a paste special function, I get an 1004 error. Would love advice on optimising this!
我对 VBA 非常陌生，并且正在尝试自动化工作中的一个过程，我需要从 6 个工作表的数组中提取选定的单元格并将它们合并到另一个工作表中。我的代码有效，但有点“笨拙” - 我正在使用 excel 复制和粘贴功能，但似乎无法找到远离复制和粘贴功能的好的解决方案。当我尝试添加粘贴特殊功能时，出现 1004 错误。想要优化这个的建议！
For each sheet to be copied, cells are marked in the first column with "1", "0" or left blank - if the cells are "1" or "0", I copy the other cells in the row to the consolidated sheet. There are some gaps in between rows, so I opted to use a For-Loop instead of a Do-While statement.
对于要复制的每个工作表，单元格在第一列中标记为“1”、“0”或留空 - 如果单元格为“1”或“0”，我将行中的其他单元格复制到合并工作表. 行之间有一些间隙，所以我选择使用 For-Loop 而不是 Do-While 语句。
I've attached the code as follows:
我附上了代码如下：
Sub TEST()
Dim i As Integer 'copying row counter for each sheet to be copied
Dim j As Integer 'pasting row counter in consolidated sheet
Dim cal(1 To 6) As String 'copied sheetname
cal(1) = "Picks"
cal(2) = "Eats"
cal(3) = "Night Out"
cal(4) = "Active"
cal(5) = "Family"
cal(6) = "Arts"
Dim x As Integer
Dim y As Integer 'column for date
Dim z As Integer 'max row to run till
y = 1 'column checked in each sheet where condition for copying is met
z = 300 'number of rows to check in each sheet
j = 1
For x = 1 To 6
For i = 1 To z
If Sheets(cal(x)).Cells(i, y) = "0" Or Sheets(cal(x)).Cells(i, y) = "1" Then
Sheets(cal(x)).Select
Range(Sheets(cal(x)).Cells(i, 2), Sheets(cal(x)).Cells(i, 10)).Select
Selection.Copy
Application.Goto ActiveWorkbook.Sheets(Consolidated).Cells(j, 1)
ActiveSheet.Paste
Else
j = j - 1
End If
j = j + 1
Next i
Next x
End Sub
Again I would love to optimise this code, using another method instead of copy-and-paste. Also I tried:
我再次希望使用另一种方法而不是复制粘贴来优化此代码。我也试过：
Application.Goto ActiveWorkbook.Sheets(Consolidated).Cells(j, 1)
ActiveSheet.PasteSpecial Operation:=xlPasteValues
Which resulted in a 1004 error. Would love to know what went wrong.
这导致了 1004 错误。很想知道出了什么问题。
回答by Jon Crowell
You're getting the error because you're attempting to paste into the activesheet instead of into a range on the activesheet, and because you have the wrong argument for the PasteSpecialmethod.
您收到错误是因为您试图粘贴到活动表中而不是粘贴到活动表上的范围中，并且因为您对该PasteSpecial方法有错误的参数。
This will work, although it's not what you want to do: (see CopyWithoutClipboardfurther below for a better alternative)
这会起作用，尽管这不是您想要做的：（CopyWithoutClipboard有关更好的选择，请参见下文）
Sub PasteIntoGoto()
Sheets("sheet1").Range("A1").Copy
Application.Goto ActiveWorkbook.Sheets("Sheet3").Cells(1, 1)
ActiveSheet.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
End Sub
Note the range inserted in between ActiveSheetand PasteSpecialand Paste:=instead of Operation:=.
请注意插入在ActiveSheetandPasteSpecial和Paste:=而不是之间的范围Operation:=。
You're right in wanting to optimize your code. Maybe the most important guideline in Excel VBA development is to never select anything, which can cause all kinds of problems. In your first example, you are using .Selectexplicitly, and in the second example, .GoTois effectively doing the same thing.
您想要优化代码是对的。也许 Excel VBA 开发中最重要的准则是永远不要选择任何东西，这可能会导致各种问题。在您的第一个示例中，您正在.Select显式使用，而在第二个示例中，.GoTo您实际上是在做同样的事情。
Rather than selecting a sheet, copying a range, selecting another sheet, and pasting into another range, you can write a copy of the data to the target range (either on the same sheet or on another one) like this:
您可以像这样将数据副本写入目标范围（在同一工作表上或另一个工作表上），而不是选择工作表、复制范围、选择另一张工作表并粘贴到另一个范围内：
Sub CopyWithoutClipboard()
Sheets("sheet1").Range("A1").Copy Sheets("sheet2").Range("A1")
End Sub
Obviously you can use variables in place of the hard-coded objects in the snippet above.
显然，您可以使用变量代替上面代码片段中的硬编码对象。
