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

meisch

New Member
Joined
Oct 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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:

VBA Code:
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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,514
Office Version
  1. 2013
Platform
  1. Windows
Based on the narrative in the OP.
VBA Code:
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

New Member
Joined
Oct 22, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Awesome, very much appreciated!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,745
Messages
5,543,963
Members
410,586
Latest member
acadavid86
Top