excel - 查找标题，以相同的顺序复制到另一张表

Question

是否可以在 VBA 中按标题列搜索？

期望的目标是找到标题时，将整列复制到 sheet2。标题应按照它们在标题列中出现的顺序进行复制。复制 3 列后，应插入空白列，然后再插入 3 列，然后再插入空白列，依此类推。

当标题在列表中时，我设法做到了。但是当他们在列时我很挣扎，在 VBA 中没有那么有经验。

标签： excelvba

Answer 1

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim LastRow As Long, header As Range, foundHeader As Range, lCol As Long, srcWS As Worksheet, desWS As Worksheet
    Set srcWS = Sheets("Sheet1")
    Set desWS = Sheets("Sheet2")
    LastRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lCol = desWS.Cells(1, Columns.Count).End(xlToLeft).Column
    For Each header In desWS.Range(desWS.Cells(1, 1), desWS.Cells(1, lCol))
        Set foundHeader = srcWS.Rows(1).Find(header, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundHeader Is Nothing Then
            srcWS.Range(srcWS.Cells(2, foundHeader.Column), srcWS.Cells(LastRow, foundHeader.Column)).Copy desWS.Cells(2, header.Column)
        End If
    Next header
    Application.ScreenUpdating = True
End Sub