VBA: Copy multiple columns from a Sheet into one column from another Sheet

Question

I have multiple columns with a specific distance between them and i want to copy all data into one column.

I have a variable n=29.

The distance between columns is allways 9. the number of total columns is n*(9+1)=290 witch corespond to column "KD"

The sheet with bellow data is named "Importat"

 |  B  |....|  L  |....|  V  |...| AF |..until.. |  KD  |
---------------------------------------------------------
1|
2|  10 |....| 79  |....| 21  |...| 41 |..........|  22  |
3|  13 |....| 55  |....| 51  |...|    |..........|  56  |
4|  16 |....|     |....|     |...|    |..........|  67  |

And the results must be like this in sheet "COD+DATA"

  |  K  |
 ---------
 1|     |
 2| 10  |
 3| 13  |
 4| 16  |
 5| 79  |
 6| 55  |
 7| 21  |
 8| 51  |
 9| 41  |
.......  
 x| 22  |
 y| 56  |
 z| 67  |

Any advice how can I do that ?

What have you tried so far? you could do this with a simple sub but would like to see what you have tried so far — Zac
– Zac, Commented Oct 21, 2016 at 12:01
I've tried when i copy the data from .xml columns, witch is opened one by one, but no succes. If i paste the query you don't understand because i have a lot of variables. I don't want all the query made by you, all I want is a hint ...thanks — BOB
– BOB, Commented Oct 21, 2016 at 12:12
What I don't understand about your desired outcome is you have everything in 1 row, but the first 9 align with numbers I thought were row numbers, but then it goes to x y z. What data fills the first column? — Tim Wilkinson
– Tim Wilkinson, Commented Oct 21, 2016 at 12:39

Zac · Accepted Answer · 2016-10-21 14:51:12Z

2

Maybe I've misunderstood your question but from what I understand, you have data in Importat sheet in a workbook. Data is in columns and columns are always separated by 9 columns (going all the way up to column KD). Below is one approach you can use:

Sub GetDataFromImportatSheet()

    Dim oIWS As Worksheet: Set oIWS = ThisWorkbook.Worksheets("Importat")
    Dim oCWS As Worksheet: Set oCWS = ThisWorkbook.Worksheets("COD+DATA")
    Dim intLastColumn As Integer: intLastColumn = oIWS.Cells(2, oIWS.Columns.Count).End(xlToLeft).Column
    Dim intLastRow As Integer
    Dim intColCounter As Integer
    Dim intCurRow As Integer
    Dim intCWSRow As Integer

    intCWSRow = 0

    For intColCounter = 2 To intLastColumn Step 9

        intLastRow = oIWS.Cells(oIWS.Rows.Count, intColCounter).End(xlUp).Row

        For intCurRow = 1 To intLastRow

            If Len(Trim(oIWS.Cells(intCurRow, intColCounter))) <> 0 Then

                intCWSRow = intCWSRow + 1
                oCWS.Cells(intCWSRow, "J").Value = intCWSRow
                oCWS.Cells(intCWSRow, "K").Value = oIWS.Cells(intCurRow, intColCounter)

            End If

        Next

    Next

End Sub

edited Oct 21, 2016 at 14:51

answered Oct 21, 2016 at 13:25

Zac

1,9501 gold badge10 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Zac Over a year ago

I agree with @Thomas, your columns don't match your requirement of 9 columns apart but I've ignored your example and gone with '9 columns apart'

user6432984 Over a year ago

+1 your code looks good. I you should use For intColCounter = 2 To intLastColumn Step 9 instead of intColCounter = intColCounter + 8. I personally use single letter variables for counters and indices. When I see them I know they are counters. If you get a change check out: Refactored Code My use of Target is a little goofy, it may take away from the readibility of the code.

Zac Over a year ago

@ThomasInzina you are absolutely right. Totally forgot about the Step option: update code as per your spot. Cant get to the Refactor Code link from work but will have a look at it tonight

user6432984 · Accepted Answer · 2016-10-21 13:04:48Z

2

You example data doesn't follow a consistent pattern.

Dim y As Long

For y = 2 To 300 Step 10
    Debug.Print Split(Columns(y).Address(False, False), ":")(0); ",";
Next

answered Oct 21, 2016 at 13:04

user6432984

Comments

Amit Kohli · Accepted Answer · 2016-10-21 11:21:30Z

0

What you want to do is reshape your data. I created a complex macro to do this both ways, list -> table and table -> list. Check it out: http://amitkohli.com/change-lists-into-tables-and-tables-into-lists/

You might have to create an intermediate table where all the data is next to eachother though, not 9 columns apart.

answered Oct 21, 2016 at 11:21

Amit Kohli

2,9702 gold badges30 silver badges47 bronze badges

Collectives™ on Stack Overflow

VBA: Copy multiple columns from a Sheet into one column from another Sheet

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related