Copy contiguous columns, paste to non-contiguous columns

StephenArg

New Member
Joined
Mar 22, 2019
Messages
5
Hi there,

I have a workbook with integers stored in 6 contiguous columns and 3 rows. ("source WB")
For example: A1:F3. However, this range changes from week to week, so the selection of the source range needs to be dynamic.

I have another workbook ("destination WB") where I want to paste special (values) the above data, but into non-contiguous columns, as the intermediate columns have a formula in them that I don't want to be overwritten:
For example:
G24 in destination WB: Paste data from A1 from source WB
H24 in destination WB: Ignore as contains a formula
I24 in destination WB: Paste data from B1 from source WB
J24 in destination WB: Ignore as contains a formula
K24 in destination WB: Paste data from C1 from source WB

etc, etc across the six columns A:F from source WB being pasted to columns G:Q in destination WB and the three rows 1:3 being pasted to rows 24:26.

Essentially, I'm looking for a way to split the range of data in the clipboard into up to 18 individual values (i.e. to create an array) that can then be pasted special into the destination WB, one value at a time. In fact, ideally, I would like to be able to select any number of columns from the source WB, from 2 to 6 and then have the VBA code use the number of columns to determine how to paste the result into the destination WB, but I can manage with a fixed column range. The number of rows will always be identical, i.e. three contiguous rows from the source WB will always paste to three contiguous rows in the destination WB.

I hope that my explanation is clear and would be grateful for any help you can give to solve this.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Does this do what you want. The code is using your two Workbook names ("source WB" as a .xlsm & "destination WB" as a .xlsx). Both workbooks must be open. When the InputBox opens you can select any number of Rows and Columns as your source range (select with mouse). The selected information will always write to the "destination WB" starting in cell G24 and continuing as per your stated requirement above. The source information can be any range, it does not have to start at Cell A1, but it must be a contiguous range.

VBA Code:
Sub MoveData()
    
    Dim wbD As Workbook: Set wbD = Workbooks("destination WB.xlsx")
    Dim wbS As Workbook: Set wbS = Workbooks("source WB.xlsm")
    Dim arr
    Dim f As Long, c As Long, col As Long, rw As Long
    
    If Not ActiveWorkbook.Name = "source WB.xlsm" Then wbS.Activate
    arr = Application.InputBox(prompt:="Please Select a Range of Cells", Type:=64)
    For f = 1 To UBound(arr, 1)
        col = 0
        For c = 1 To UBound(arr, 2)
            wbD.ActiveSheet.Range("G24").Offset(rw, col) = arr(f, c)
            col = col + 2
        Next
        rw = rw + 1
    Next
    
End Sub
 
Upvote 0
Try this, change the names of the books in the code.
Note: Both books must be open.

VBA Code:
Sub Copy_contiguous_columns()
  Dim rng As Range, c As Range, n As Long
  Workbooks("source WB.xlsx").Activate
  On Error Resume Next
  Set rng = Application.InputBox("Select a Range of Cells ", "Book", Default:=Selection.Address, Type:=8)
  If rng Is Nothing Then Exit Sub
  On Error GoTo 0
  For Each c In rng.Columns
    Workbooks("destination WB.xlsx").Sheets(1).Range("G24").Offset(, n).Resize(c.Rows.Count, 1).Value = c.Resize(c.Rows.Count, 1).Value
    n = n + 2
  Next
End Sub
 
Upvote 0
Thanks to igold and DanteAmor for their answers, both of which do exactly what I was looking for. I'm not sure whether one method is faster than the other. When I stepped through the code, I noticed that DanteAmor's answer dropped the results for all three rows of each column in one pass, whereas igold's did one cell at a time. However, I am most grateful to both of you for your answers.
As an avid VBA coder myself, I would love to have an explanation as to how these two answers work. I think that I mainly understand igold's code, but DanteAmor's is still leaving me puzzled. So, if you don't mind, it would be great for me to learn from your code and understand the method.
Thanks, once again
Stephen
 
Upvote 0
As an avid VBA coder myself, I would love to have an explanation as to how these two answers work. I think that I mainly understand igold's code, but DanteAmor's is still leaving me puzzled. So, if you don't mind, it would be great for me to learn from your code and understand the method.

Hi @StephenArg, I will gladly explain what the line does.

I suppose that with the following you have no problems, it is only to store the selected cell range in the rng object.
VBA Code:
  On Error Resume Next
  Set rng = Application.InputBox("Select a Range of Cells ", "Book", Default:=Selection.Address, Type:=8)
  If rng Is Nothing Then Exit Sub
  On Error GoTo 0

Let's move on to the following
VBA Code:
  For Each c In rng.Columns
    Workbooks("destination WB.xlsx").Sheets(1).Range("G24").Offset(, n).Resize(c.Rows.Count, 1).Value = c.Resize(c.Rows.Count, 1).Value
    n = n + 2
  Next

The For reads each of the columns in the selected range.

In the destination WB, in the range G24, but with a growth of n rows (Resize), if you select 3 rows then the range grows from G24 to G24:G26, the values from source book of the selected column are placed but also with a growth of n lines.

Finally, for each selected column the destination moves 2 columns (n = n +2) to the right (Offset (, n)).

At the beginning of the cycle n = 0, that is why the destination is G24, the next cycle will be at I24.

I hope the above is of your help.
And thanks for the feedback.
 
Upvote 0
Hi @StephenArg, I will gladly explain what the line does.

I suppose that with the following you have no problems, it is only to store the selected cell range in the rng object.
VBA Code:
  On Error Resume Next
  Set rng = Application.InputBox("Select a Range of Cells ", "Book", Default:=Selection.Address, Type:=8)
  If rng Is Nothing Then Exit Sub
  On Error GoTo 0

Let's move on to the following
VBA Code:
  For Each c In rng.Columns
    Workbooks("destination WB.xlsx").Sheets(1).Range("G24").Offset(, n).Resize(c.Rows.Count, 1).Value = c.Resize(c.Rows.Count, 1).Value
    n = n + 2
  Next

The For reads each of the columns in the selected range.

In the destination WB, in the range G24, but with a growth of n rows (Resize), if you select 3 rows then the range grows from G24 to G24:G26, the values from source book of the selected column are placed but also with a growth of n lines.

Finally, for each selected column the destination moves 2 columns (n = n +2) to the right (Offset (, n)).

At the beginning of the cycle n = 0, that is why the destination is G24, the next cycle will be at I24.

I hope the above is of your help.
And thanks for the feedback.

Thanks for that explanation, which makes it very clear. It's always great to learn new techniques that extend my VBA knowledge.
Best wishes,
Stephen
 
Upvote 0

Forum statistics

Threads
1,212,938
Messages
6,110,789
Members
448,297
Latest member
carmadgar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top