Excel VBA：将单元格从多个工作表复制到单个工作表 [英] Excel VBA: Copy cells from multiple sheets to a single sheet

问题描述

我对VBA很新，并且正在尝试在工作中自动化一个进程，我需要从6个数组中提取选定的单元格，并将它们合并到另一个工作表中。代码我有工作，但有点笨重 - 我正在使用excel复制和粘贴功能，但似乎找不到一个很好的解决方案远离复制和粘贴功能。而当我尝试添加一个粘贴特殊功能，我得到一个1004错误。对于要优化的建议，请给我一个建议！

对于要复制的每个工作表，单元格在第一列中用1，0标记或留空 - 如果单元格是1或0，我将该行中的其他单元格复制到合并表单。行之间有一些差距，所以我选择使用For-Loop而不是Do-While语句。

我附上了代码如下： / p>

  Sub TEST（）
 Dim i As Integer'copy row counter for each sheet to be copied 
 Dim j As Integer'综合表格中的粘贴行计数
 Dim cal（1至6）As String'复制的表单
 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日期的整数列
 Dim z As Integer'max row to run until 
 
y = 1'column checked在每张表中满足复印条件
z = 300'每张表中要检查的行数
 
j = 1 
 
对于x = 1至6 
 
对于i = 1到z 
如果表格（cal（x））。Cells（i，y）=0或者ets（cal（x））。单元格（i，y）=1然后
表格（cal（x））选择
 Range（Sheets（cal（x） 2），Sheets（cal（x））。Cells（i，10））。选择
 Selection.Copy 
 Application.Goto ActiveWorkbook.Sheets（Consolidated）.Cells（j，1）
 ActiveSheet.Paste 
 Else 
j = j  -  1 
结束如果
j = j + 1 
下一个i 
下一个x 
 End Sub 
  

再次，我想要优化此代码，使用其他方法，而不是复制和粘贴。另外我试过：

  Application.Goto ActiveWorkbook.Sheets（Consolidated）.Cells（j，1）
 ActiveSheet。 Paste特殊操作：= xlPasteValues 
  

导致1004错误。想知道发生了什么问题。

解决方案

您正在收到错误，因为您尝试粘贴到活动表单在$活动表单上的范围内，因为您有 PasteSpecial 方法的错误参数。

工作，虽然不是你想做的：（见 CopyWithoutClipboard 进一步下面更好的选择）

  Sub PasteIntoGoto（）
 Sheets（sheet1）。Range（A1）。复制
 Application.Goto ActiveWorkbook.Sheets（Sheet3）。 ，1）
 ActiveSheet.Cells（1，1）.PasteSpecial粘贴：= xlPasteValues 
 End Sub 
  

注意插入 ActiveSheet 和 PasteSpecial 和粘贴之间的范围： = 而不是操作：= 。

您正在要优化你的代码也许在Excel VBA开发中最重要的指导方针是不要选择任何，这可能会导致各种问题。在第一个例子中，您正在使用。显式选择，而在第二个例子中， .GoTo 正在做

而不是选择一个工作表，复制一个范围，选择另一个工作表，并粘贴到另一个范围内，可以将数据的副本写入目标范围（或者在同一张表上或另一张）：

  Sub CopyWithoutClipboard（）
表格（sheet1 ）.Range（A1）。复制表（sheet2）。范围（A1）
结束子
  

显然，您可以在上面的代码片段中使用变量代替硬编码的对象。

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!

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.

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.

解决方案

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 PasteSpecial method.

This will work, although it's not what you want to do: (see CopyWithoutClipboard further below for a better alternative)

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 ActiveSheet and PasteSpecial and Paste:= instead of 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 .Select explicitly, and in the second example, .GoTo is effectively doing the same thing.

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.

这篇关于Excel VBA：将单元格从多个工作表复制到单个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！