Copy contiguous columns, paste to non-contiguous columns

StephenArg

New Member
Joined
Mar 22, 2019
Messages
4
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

Well-known Member
Joined
Jul 8, 2014
Messages
2,429
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
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
 

StephenArg

New Member
Joined
Mar 22, 2019
Messages
4
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,919
Office Version
2007
Platform
Windows
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.
 

StephenArg

New Member
Joined
Mar 22, 2019
Messages
4
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
 

Forum statistics

Threads
1,078,442
Messages
5,340,302
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top