Stacking Many Columns into one Column

[dawnandrea2018]

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:

	A	B	C	D	E	F	G
1	Question code	Question	Person 1	Person 2	Person 3	Person 4	..
2	1a	Worked on..	1	66	5	2	..
3	1b	Put together..	3	14		4	..
4	1c	Made...	4	13	3		..
..	..	..	..	..	..	..	..

<tbody>
</tbody>

Dump Data sheet looks like this:

	A	B	C	D	E
1	Year	Indicator	Response	...	...
2	2017	...
3	2017	...
4	2017	...
5	2017	...

<tbody>
</tbody>

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

	A	B	C	D	E
1	Year	Indicator	Response	...	...
2	2017	..	..
3	2017	..	..
4	2017	..	..
5	2017	..	..
6	2017	...	1
7	2017	..	3
8	2017	..	4
9	2017	..	66
10	2017		14
11	2017		13
12	2017		5
13	2017
14	2017		3
15	2017		2
16	2017		4
17	2017

<tbody>
</tbody>

I would really appreciate any help. Thanks!

 

[JLGWhiz]

See if this Will do what you want.

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.

 

[dawnandrea2018]

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]

See if this Will do what you want.

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]

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]

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.

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]

Based on the above, I think the changes in red font would do what you want.

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]

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]

I just noticed that his was missing a qualifier.

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