☰

Excel 根据单元格值将行复制到另一张表

tag-icon • tag-icon vba macros
Excel 根据单元格值将行复制到另一张表

我想根据单元格值复制行（Emp_Section; Emp_Section包含正面） 从工作表1到工作表2。

我有以下脚本，它将复制所有行

Sub EmpCopy()
    Dim myCols As Variant
    Dim lastRow As Long
    Dim c As Long

    Sheets("Sheet1").Activate

'   Set columns you want to loop through in an array
    myCols = Array("D", "B", "C", "F")

'   Loop through columns array
    For c = LBound(myCols) To UBound(myCols)
'   Find last row in column W with data
        lastRow = Sheets("Sheet1").Cells(Rows.Count, myCols(c)).End(xlUp).Row
'       Copy data from Model sheet to summary sheet
        Sheets("Sheet1").Range(Cells(2, myCols(c)), Cells(lastRow, myCols(c))).Copy
        Sheets("Sheet2").Cells(2, c + 1).PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    Next c
     Sheets("Sheet2").Activate
End Sub

附有截图。

有人愿意向我解释我需要做什么吗

在此处输入图片描述

答案1

用作模板：

dim src() 'for source data'
dim tmp() 'for temporary data'
dim dst() 'for destination data'

src=sheets("source").range("Xm:Yn").value 'get source data'
redim tmp(ubound(scr,1),4)                'destination have 4 columns'
n=lbound(tmp,1)

for i = lbound(src,1) to ubound(src,1) 'process source row by row'
    if src(i,5) = 'Front' then         'if marker found, store data in temp array'
        tmp(n,1) = src(i,4)            '4th source column to 1st dest column'
        tmp(n,2) = src(i,2)
        tmp(n,3) = src(i,3)
        tmp(n,4) = src(i,6)
        n=n+1                          'increase found rows count'
    end if
next i

redim dst(n-1, 4)    'for found rows only'

for i = lbound(dst,1) to n-1 'reassing temp to dest, skip excess elements'
    dst(i,1) = tmp(i,1)
    dst(i,2) = tmp(i,2)
    dst(i,3) = tmp(i,3)
    dst(i,4) = tmp(i,4)
next i

sheets("destination").range("Xm:Yn").value = dst 'store processed data'

仔细调试代码 - 模板可能包含一些打印错误或错误。

相关内容