根据列名称将一个Excel数据复制并粘贴到另一个Excel中，包括空白单元格 [英] copy and paste the data one excel to another excel based on column name include blank cells

问题描述

我只是想根据列名将数据从一个Excel提取到另一个Excel.源excel名称是"iTerm Export.xls".工作表名称为"export(1)".列标题为资产".因此，在运行以下宏资产列数据时，必须将其复制并粘贴到其他Excel中，即("iTerm指标Report.xlsx")

I am just trying to pull the data from one excel to another excel based on column name. Source excel name is "iTerm Export.xls". Sheet name is "export(1)". column heading is "Asset". so when run the below macro Asset column datas has to be copy and paste into the other excel i.e("iTerm metrics Report.xlsx")

但是我的问题是，如果资产"列中任何地方都有空白单元格，例如:资产"列中有50个数据行.但是25号和30号是一个空白单元格.当我运行一次宏时，将24行复制并粘贴到其他Excel中.但我需要将所有五十行复制并粘贴到其他Excel中，包括空白行

But my issue if there is a blank cell anywhere in the Asset column, Ex: there are 50 data rows in Asset column. But 25th and 30 is a blank cell. when i am running the macro once 24 rows copy and paste in the other excel. but i need all fifty rows has to be copy and paste include blank row in other excel

Windows("iTerm Export.xls").Activate
Sheets("export(1)").Select
Cells.Find(What:="Asset", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("iTerm metrics Report.xlsx").Activate
Sheets("Raw Data from iTerm").Select
Range("A2").Select
ActiveSheet.Paste

请帮助我.

谢谢

推荐答案

阿鲁尔

我不建议您使用.选择，因为这是导致错误的主要原因.例如，请参见此线程

I won't suggest you to use .Select as it is a major cause of errors. See this thread for example

运行时间错误"1004":范围类的选择方法在VBA 2003中失败

话虽如此，我建议直接执行所需的操作，而不要先执行 .Select .另外，您如何打开工作簿"iTerm Export.xls"和"iTerm指标Report.xlsx"?如果在运行宏时它们已经打开，则可以使用 .Activate ，否则请设置工作簿变量，然后打开工作簿.这样，您也可以避免使用 .Activate .让我知道是否是这种情况，我将提供一个示例.

Having said that, I would recommend directly performing the action that you want rather than doing a .Select first. Also how are you opening the workbooks "iTerm Export.xls" and "iTerm metrics Report.xlsx"? If they are already opened when you are running the macro then it's ok to use .Activate else set a workbook variable and then open the workbooks. That ways you can avoid using .Activate as well. Let me know if this is the case and I will provide a sample.

.Select 和 .Activate 的另一个缺点是，它会大大降低代码的速度.

The other drawback of .Select and .Activate is that it slows down your code drastically.

您的上述代码也可以编写如下.这是使用 .Find 而不是直接使用 .Activate 的正确方法.原因是如果找不到匹配项，代码将在下面的行中崩溃.

Your above code can also be written as below. This is the correct way to use .Find instead of directly using .Activate. The reason being the code will crash on the below line if no match is found.

Cells.Find(What:="Asset"，After:= ActiveCell，LookIn:= xlFormulas，LookAt _:= xlPart，SearchOrder:= xlByRows，SearchDirection:= xlNext，MatchCase:= False，_ SearchFormat:= False).激活

因此，建议检查是否找到该值，然后继续.

Hence it is advisable to check if the the value was found and then proceed.

尝试此代码，看看这是否是您想要的?(未测试)

Try this code and see if this is what you want? (UNTESTED)

Sub Sample()
    Dim aCell As Range

    Windows("iTerm Export.xls").Activate

    With Sheets("export(1)")
        Set aCell = .Cells.Find(What:="Asset", LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

        '~~> Check if "Asset is found
        If Not aCell Is Nothing Then
            '~~> get the lastrow of the column which has "Asset"
            lastRow = .Range(Split(Cells(, aCell.Column).Address, "$")(1) & _
            .Rows.Count).End(xlUp).Row

            Windows("iTerm metrics Report.xlsx").Activate

            .Range( _
            Split(Cells(, aCell.Column).Address, "$")(1) & aCell.Row & _
            ":" & _
            Split(Cells(, aCell.Column).Address, "$")(1) & lastRow _
            ).Copy _
            Sheets("Raw Data from iTerm").Range("A2")
        Else
            MsgBox "Asset not found"
        End If
    End With
End Sub

HTH

Sid

这篇关于根据列名称将一个Excel数据复制并粘贴到另一个Excel中，包括空白单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！