excel - 将选定的动态数据列从一个工作表复制并粘贴到另一个工作表

Question

我想将数据从表 1 中的特定列复制到表 2 中的特定列。有 20 个这样的列，并且该映射维护在一个表中，例如

我已经编写了代码来搜索列名（源和目标表），但无法将数据从源列（动态范围）复制到目标列。

 Sub search_validate()

    Dim j As Integer

    Dim sourcSearch, destSearch As String
    Dim sCell, dCell As Range

    For j = 3 To 20

        sourcSearch = Sheet6.Range("Z" & j).Value ' pickup selected source column name 
        destSearch = Sheet6.Range("AA" & j).Value ' pickup selected destination column name 

        Set sCell = Sheet1.Rows(2).Find(What:=sourcSearch, LookIn:=xlValues, _
        LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

        Set dCell = Sheet2.Rows(2).Find(What:=destSearch, LookIn:=xlValues, _
          LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
          MatchCase:=False, SearchFormat:=False)

    '  sCell.Address or sCell.Column to get source column header address but data starts after this column. dynamic range

    '  dCell.Address or dCell.Column to get destination column header address. no data in destination column except header.

    Next j

End Sub

标签： excelvba

Answer 1

这应该将数据附加到目标列的末尾

    If Not sCell Is Nothing And Not dCell Is Nothing Then
        Dim Source As Range, Target As Range
        Set Source = Intersect(Sheet1.UsedRange, sCell.EntireColumn).Offset(1)
        Set Target = Sheet2.Cells(Sheet2.Rows.Count, dCell.Column).End(xlUp).Offset(1)
        Source.Copy Destination:=Target
    End If