Copy contiguous columns, paste to non-contiguous columns

[StephenArg]

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.

 

[igold]

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.

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

 

[DanteAmor]

Try this, change the names of the books in the code.
Note: Both books must be open.

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

 

[StephenArg]

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

 

[DanteAmor]

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.

  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

  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.

 

[StephenArg]

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.

  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

  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

 

[DanteAmor]

Again a pleasure