Vba在2张纸上搜索列匹配，并从第2页到第1页粘贴行。 [英] Vba search for column match in 2 sheets and paste row from sheet 2 to sheet 1.

问题描述

您好我想问。



我的情况是

表1包括从A列到Z的值。

表5包括来自A栏到Z栏。



两张纸，Z栏都有共同的价值。



我想使用运行到
的VBA代码1.将（表5）的Z列与Sheet的Z列匹配1.

2.如果找到匹配，则复制，粘贴表5（*匹配）的行到表1的相应行。



请帮忙！



我的尝试：



使用.Range（Z1：Z和LastRow）

。自动填充字段：= 1，Criteria1：== *& strSearch& *

。偏移（1,0）.SpecialCells（xlCellTypeVisible）.Cells.Copy

Sheets（Sheet5）。激活

DestinationRange.PasteSpecial

结束

Hi i would like to ask.

My situation is
Sheet 1 includes value from Column A to Z.
Sheet 5 includes value from Column A to Z.

Both sheet, Column Z has common value.

I would like to use a VBA code that run to
1. match Column Z of (Sheet 5) to Column Z of Sheet 1.
2. If match found, then copy, paste the row from Sheet 5(*that match), to Sheet 1 respective row.

Please help!

What I have tried:

With .Range("Z1:Z" & LastRow)
.Autofilter Field:=1, Criteria1:="=*" & strSearch & "*"
.Offset(1,0).SpecialCells(xlCellTypeVisible).Cells.Copy
Sheets("Sheet5").activate
DestinationRange.PasteSpecial
End With

推荐答案

根据您在帖子中提到的内容，这就是代码的样子。代码需要

1.找出需要比较多少行

2.循环每一行，比较Sheet5和Sheet1中的Z列值

3.如果匹配，将单元格A：Y从Sheet5复制到Sheet1



在Excel 2016上测试。

Based on what you mentioned in the post, this is how the code should look like. the code need to
1. Find out how many rows need to compare
2. Loop each row, compare the Z column value in Sheet5 and Sheet1
3. If match, copied the cells A:Y from Sheet5 to Sheet1

Tested on Excel 2016.

Sub CP_CopyPasteRow()
    Dim lastrow As Long

    'Disable Alert (Warning) Messages in Excel
    Application.DisplayAlerts = False
    
    With Sheets("Sheet5")
    'get the last row index with data
        lastrow = .Cells(.Rows.Count, "Z").End(xlUp).Row
        
        For r = 1 To lastrow
            'loop each to matzh Z column, if match copy A:Y to Sheet 1
            If Worksheets("Sheet1").Cells(r, Range("Z" & 1).Column).Value = .Cells(r, Range("Z" & 1).Column).Value Then
                .Rows(r).Copy
                Worksheets("Sheet1").Range("A" & r & ":Y" & r).PasteSpecial xlPasteValues
            End If
        Next r
        
    End With

'enable alert
   Application.DisplayAlerts = True
End Sub

这篇关于Vba在2张纸上搜索列匹配，并从第2页到第1页粘贴行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！