Excel VBA-复制Mutiple柱，不按顺序顺序排列

Question

我想从一张纸复制到另一张。宏应通过名称识别工作表：

Dim Wb1 As Workbook, wb2 As Workbook, wB As Workbook
Dim rngToCopy As Range
Dim NewEnd As Long
Dim NewEnd2 As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("CALC").Select
Worksheets("CALC").Range("B5:J25000").ClearContents
For Each wB In Application.Workbooks
    If Left(wB.Name, 4) = "15B2" Then
        Set Wb1 = wB
        Exit For
    End If
Next
If Not Wb1 Is Nothing Then
    Set wb2 = ThisWorkbook
    With Wb1.Sheets("Data")
        Set rngToCopy = .Range("F7, H7, N7", .Cells(.rows.Count, "F").End(xlUp))
    End With
    wb2.Sheets("CALC").Range("B5:D5").Resize(rngToCopy.rows.Count).Value = rngToCopy.Value
End If

这条线给了我一个错误：

 Set rngToCopy = .Range("F7, H7, N7", .Cells(.Rows.Count, "F").End(xlUp))

在这种情况下如何复制Mutiple列？

Answer 1

您可以使用 Union合并多列与1 Range。

Dim LastRow As Long
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row ' get last row with data from column "F"
Set rngToCopy = Application.Union(.Range("F7:F" & LastRow), .Range("H7:H" & LastRow), .Range("N7:N" & LastRow))
rngToCopy.Copy
wb2.Sheets("CALC").Range("B5").PasteSpecial xlPasteValues