Multiple Column Loop from wbA and copy/paste all data to 1 column on wkB

[meisch]

Hello all,



I'm trying to create an array loop that goes through columns A1,D1,G1, J1 (Keeping the headers) from one workbook and paste all the data to one column (keeping cell formatting) on another workbook.

The data in the copied columns (A,D,G,J) will vary every time the macro runs so I'm not sure how to set up the initial array for this.

I think I have the destination ready with:

Private Sub CommandButton2_Click()

Dim coV As Worksheet
Dim rr As Worksheet
Dim destLast As Long

Set coV = Workbooks("Cov19 Analysis.xlsm").Worksheets("Reruns To Pull")
Set rr = Workbooks("Rerun_new.xlsx").Worksheets("October")
destLast = rr.Cells(rr.Rows.Count, "A").End(xlUp).Offset(1).Row

?????? .Copy _
    rr.Range("A" & destLast)
End Sub

Any help would be much appreciated!

 

[JLGWhiz]

Based on the narrative in the OP.

Sub t()
Dim ary As Variant, i As Long
ary = Array("A", "D", "G", "J")
With ActiveSheet
    For i = LBound(ary) To UBound(ary)
        Intersect(.UsedRange, .Columns(ary(i))).Copy
        Workbooks(2).Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial xlPasteValuesAndNumberFormats  'puts all in one column of wb 2
    Next
End With
End Sub

 

[meisch]

Awesome, very much appreciated!

 

[JLGWhiz]

Awesome, very much appreciated!

You're welcome,
regards, JLG