Stacking Many Columns into one Column

dawnandrea2018

New Member
Joined
Jun 12, 2018
Messages
4
Hi,
I have a sheet with "raw data" with many columns (74+) and i need to stack them into one specific column on another sheet. This specific column has data already in it and above it.
Basically, I need to take specific columns and stack them into one column on another worksheet where there is existing data.
Its not efficient for me to copy and paste the data more than 74+. Every year there is also an increase in number of people, it may be 100 people for 2018.
For example the sheets kind of look like this:

Raw data worksheet:

ABCDEFG
1Question codeQuestionPerson 1Person 2Person 3Person 4..
21aWorked on..16652..
31bPut together..3144..
41cMade...4133..
................

<tbody>
</tbody>


Dump Data sheet looks like this:

ABCDE
1YearIndicatorResponse......
22017...
32017...
42017...
52017...

<tbody>
</tbody>

The dump sheet should like this afterwards, with responses stacked on top of each other:

ABCDE
1YearIndicatorResponse......
22017....
32017....
42017....
52017....
62017...1
72017..3
82017..4
92017..66
10201714
11201713
1220175
132017
1420173
1520172
1620174
172017

<tbody>
</tbody>


I would really appreciate any help. Thanks!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
See if this Will do what you want.
Code:
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp)).Copy sh2.Cells(Rows.Count, 3).End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
Note that you will need to validate the sheet names where noted in the code.
 
Last edited:

dawnandrea2018

New Member
Joined
Jun 12, 2018
Messages
4
Thanks!

I forgot to mention in my question that there is a table below in the raw data worksheet, and it copies this when I run the code.
How would I change this code to reflect this?

To rephrase my question, I have a range of data (i.e. F2:CB33) where I want to copy column by column and stack this in another worksheet (dump worksheet).
The dump worksheet already has existing data - I want this new data to be copied at the end of the existing data in column F. At the moment it is at F2915.
 

dawnandrea2018

New Member
Joined
Jun 12, 2018
Messages
4
See if this Will do what you want.
Code:
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells(Rows.Count, i).End(xlUp)).Copy sh2.Cells(Rows.Count, 3).End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
Note that you will need to validate the sheet names where noted in the code.
Also, what does this line do:

dataSheet.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = dataSheet.Range("A2").Value

I have been trying to get it, but I have no clue.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
Also, what does this line do:

dataSheet.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = dataSheet.Range("A2").Value

I have been trying to get it, but I have no clue.
The line of code above was intended to copy your year in column A down as far as your data in the other columns by using the value in cell A2.
I forgot to mention in my question that there is a table below in the raw data worksheet
To accomodate the table and eliminate it from the copy action, the exact range address of the table would need to be known, and if it will always be in that same address. If the table address varies from session to session then some other means of identifying where it is, like a constant and unique header value would have to be known. Otherwise, I would not know how to tell VBA to avoid copying the table.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
I have a range of data (i.e. F2:CB33) where I want to copy column by column and stack this in another worksheet (dump worksheet).
The dump worksheet already has existing data - I want this new data to be copied at the end of the existing data in column F.
Based on the above, I think the changes in red font would do what you want.
Code:
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells[COLOR=#FF0000](33,[/COLOR] i).End(xlUp)).Copy sh2.Cells(Rows.Count, [COLOR=#FF0000]6[/COLOR])[COLOR=#FF0000].[/COLOR]E[COLOR=#FF0000][/COLOR]nd(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
 

dawnandrea2018

New Member
Joined
Jun 12, 2018
Messages
4
Based on the above, I think the changes in red font would do what you want.
Code:
Sub t()
Dim i As Long, sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Raw Data") 'Edit sheet name
Set sh2 = Sheets("Dump Data") 'Edit sheet name
    For i = 3 To sh1.Cells(2, Columns.Count).End(xlToLeft).Column
        With sh1
            .Range(.Cells(2, i), .Cells[COLOR=#FF0000](33,[/COLOR] i).End(xlUp)).Copy sh2.Cells(Rows.Count, [COLOR=#FF0000]6[/COLOR])[COLOR=#FF0000].[/COLOR]End(xlUp)(3)
        End With
    Next
    sh2.Range("A3", .Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
End Sub
For some reason it skips responses when it pastes..
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
Can you post a screen shot or mock up of your sheet, or provide a link to your file on a share server. I am not sure that I have a good concept of what your sheet looks like.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,271
Office Version
2013
Platform
Windows
I just noticed that his was missing a qualifier.

Code:
sh2.Range("A3", [COLOR=#B22222]sh2[/COLOR].Cells(Rows.Count, 3).End(xlUp).Offset(, -2)) = sh2.Range("A2").Value
 
Last edited:

Forum statistics

Threads
1,081,841
Messages
5,361,630
Members
400,642
Latest member
tekster23

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top