我想将数据从工作表 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
这应该将数据附加到目标列的末尾
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句