在excel中如何比较2列中的A列和将匹配行复制到Sheet3？ [英] In excel how to compare A columns in 2 Sheets and copy matching rows to Sheet3?

问题描述

我想将Sheet1中的所有A列与Sheet2的A列进行比较，如果确切的话找到匹配，将整个匹配行从Sheet2复制到Sheet3。

提前感谢。

解决方案

从此问题修正版本的答案

  Sub CopyMatch（）
 
 Dim StartingScreenUpdateValue As Boolean 
 Dim StartingEventsValue As Boolean 
 Dim StartingCalculations As XlCalculation 
 
应用程序
 StartingScreenUpdateValue = .ScreenUpdating 
 StartingEventsValue = .EnableEvents 
 StartingCalculations = .Calculation 
 .ScreenUpdating = False 
 .EnableEvents = False 
 .Calculation = xlCalculationManual 
结束
 
 
 Dim varTestValues As Var iant 
 Dim sh1 As Worksheet 
 Dim sh2 As Worksheet 
 Dim sh3 As WorkSheet 
 
 Set sh1 = Sheets（Sheet1）
 Set sh2 = Sheets （Sheet2）
设置sh3 =表格（Sheet3）
 
使用sh2 
 varTestValues = .Range（A1，.Range（A& 。$（$）
结束
 
用sh1 
 .Range（A1，.Range（A& .Rows.Count ）.End（xlUp））_ 
 .AutoFilter字段：= 1，Criteria1：= Application.Transpose（varTestValues），运算符：= xlFilterValues 
 
 .Range（A2，sh1。 Range（A& .Rows.Count）.End（xlUp））_ 
 .SpecialCells（xlCellTypeVisible）.EntireRow.Copy sh3.Range（A1）
 
 .AutoFilterMode = False 
结束
 
应用程序
 .ScreenUpdating = StartingScreenUpdateValue 
 .EnableEvents = StartingEventsValue 
 .Calculation = StartingCalculations 
结束
 
 End Sub 
  

注意： / strong>此代码运行，假设您的数据没有标题，如果没有请指导。

记住 始终运行您的数据副本中的任何代码，而不是您的实际数据，直到您确信其正常工作为100％。

I'm new to Visual Basic and I want help to try something.

I want to compare all A columns from Sheet1 to A columns from Sheet2 and if exact match is found than copy the entire matching rows from Sheet2 to Sheet3.

Thanks in advance.

解决方案

Modified version of my answer from This Question

Sub CopyMatch()

Dim StartingScreenUpdateValue As Boolean
Dim StartingEventsValue As Boolean
Dim StartingCalculations As XlCalculation

With Application
    StartingScreenUpdateValue = .ScreenUpdating
    StartingEventsValue = .EnableEvents
    StartingCalculations = .Calculation
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With


Dim varTestValues As Variant
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As WorkSheet

Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
Set sh3 = Sheets("Sheet3")

With sh2
    varTestValues = .Range("A1", .Range("A" & .Rows.Count).End(xlUp))
End With

With sh1
    .Range("A1", .Range("A" & .Rows.Count).End(xlUp)) _
    .AutoFilter Field:=1, Criteria1:=Application.Transpose(varTestValues), Operator:=xlFilterValues

    .Range("A2", sh1.Range("A" & .Rows.Count).End(xlUp)) _
    .SpecialCells(xlCellTypeVisible).EntireRow.Copy sh3.Range("A1")

    .AutoFilterMode = False
End With

With Application
    .ScreenUpdating = StartingScreenUpdateValue
    .EnableEvents = StartingEventsValue
    .Calculation = StartingCalculations
End With

End Sub

NOTE: This code runs assuming your data has headers if it does not please advise.

REMEMBER Always run any code on a copy of your data and not your actual data until you are confident that it is working 100%.

这篇关于在excel中如何比较2列中的A列和将匹配行复制到Sheet3？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！