Direct Referencing Non Contiguous Columns

[missbanana]

Hi all, long time lurker first time poster.
I am somewhat of a VBA newbie and am trying to write a script to assign values from one of my workbooks to a template workbook, however the data in the source file is non contiguous. The below script does not cause an error. however the data from the first column in my source range is copied over to every column in the wb0 range. Wondering if there is an easier way than referencing each column individually? I came across Application.Union but not sure it will work for my situation? Any advice would be most welcome thank you!


Option Explicit
Sub Cavendish_Data()

Application.DisplayAlerts = False

Dim wb0 As Workbook
Dim wb1 As Workbook

Set wb0 = ActiveWorkbook
Set wb1 = Workbooks.Open("C:\HVO\Monthly Dust\Workbook1.xls")

'Assign values from banana daily average outputs to template
LastRow = wb1.Sheets("Block3_1_2").Range("A1").End(xlDown).Row
wb0.Sheets("Daily AVG").Range("H2:H" & LastRow & "," & "I2:I" & LastRow).Value = wb1.Sheets("Block3_1_2").Range("B2:B" & LastRow & "," & "E2:E" & LastRow).Value

 

[6StringJazzer]

Excel treats a noncontiguous range as an array of values, so you can't assign from a noncontiguous range into another noncontiguous range. You'll have to do each column individually.

 

[Fuji]

Array or Union

Sub Cavendish_Data()
  
    Dim wb0 As Workbook
    Dim wb1 As Workbook
    Dim x
    Set wb0 = ActiveWorkbook

    Set wb1 = Workbooks.Open(ThisWorkbook.Path & "\Workbook1.xls")
    'Set wb1 = Workbooks.Open("C:\HVO\Monthly Dust\Workbook1.xls")
    With wb1.Sheets("Block3_1_2")
        With .Range("a2", .Cells.SpecialCells(11))
            x = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count + 1 & ")"), Array(2, 5)) '<--- Array
            wb0.Sheets("Daily AVG").Range("H2").Resize(UBound(x, 1) - 1, 2) = x   '<--- Output Array
            Union(.Columns(2), .Columns(5)).Copy wb0.Sheets("Daily AVG").Range("H2")  '<--- Union
        End With
    End With
    wb1.Close False
End Sub