将粘贴数据从一张图纸复制到另一张图纸，只拾取过滤后的数据并保持目标列序列

Question

我需要使用excel宏自动执行将数据从一张图纸复制到另一张图纸的步骤。但以下是我面临的这个要求的问题：

1. 需要复制粘贴范围内的数据，即过滤"数据范围"=是
2. 源和目标的列顺序不同，因为大约有127列，所以无法硬编码此部分

如果您有方便的代码或逻辑来实现，请提供帮助。

Answer 1

找到了一种简单的实现方法，将其发布在这里供其他人使用。

Sub Reorganize_columns()
Dim v As Variant, x As Variant, findfield As Variant
Dim oCell As Range
Dim rng As Range
Dim iNum As Long
Dim sht_source As Worksheet, sht_target As Worksheet


Set sht_source = ActiveWorkbook.Sheets("Data")
Set sht_target = ActiveWorkbook.Sheets("Macro")

sht_source.Range("A1").AutoFilter Field:=1, Criteria1:="Yes"

Set rng = sht_target.Range("A1:HS1")

For Each cell In rng
iNum = iNum + 1
findfield = cell.Value
Set oCell = sht_source.Rows(1).Find(What:=findfield, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
sht_source.Columns(oCell.Column).Copy
sht_target.Columns(iNum).PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Next cell

ActiveWorkbook.Save

MsgBox "Completed"
End Sub