Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou

Try this code.
1) Chage rows of source as necessary F4:F53
2) Adjust the column of the destination as needed = Range("A"

LastRow = Sheets("Results"). _
Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Questionnaire").Range("F4:F53").Copy
Range("A" & (LastRow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

"MLK" wrote:

Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou

Hi, the Quesitonnaire worksheet does not store the responses in 50
consecutive rows. There are ranges like F4 to F10, then F13 to f16, then the
rest are single cell values every 2nd or 3rd row.

"Joel" wrote:

Try this code.
1) Chage rows of source as necessary F4:F53
2) Adjust the column of the destination as needed = Range("A"

LastRow = Sheets("Results"). _
Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Questionnaire").Range("F4:F53").Copy
Range("A" & (LastRow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

"MLK" wrote:

Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou

Ok, I got it working the way I need it.

I took your code and tweaked it a little to be able to paste all the
separate cells (which meant copying the code alot)... but it works!

Thanks very much for your assistance! Mary-Lou



"MLK" wrote:

Hi, the Quesitonnaire worksheet does not store the responses in 50
consecutive rows. There are ranges like F4 to F10, then F13 to f16, then the
rest are single cell values every 2nd or 3rd row.

"Joel" wrote:

Try this code.
1) Chage rows of source as necessary F4:F53
2) Adjust the column of the destination as needed = Range("A"

LastRow = Sheets("Results"). _
Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Questionnaire").Range("F4:F53").Copy
Range("A" & (LastRow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

"MLK" wrote:

Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou

You have 3 choices
1) Copy then blanks row
2) Change SkipBlanks:=True. This may be a problem if some forms have blanks
in some entries. The data will not allign on the new worksheet.
3) Do the copy in smaller pieces. Or change the range to be pieces
Sheets("Questionnaire").Range("F4:F10","F13:F16"," F20","F27").Copy

"MLK" wrote:

Hi, the Quesitonnaire worksheet does not store the responses in 50
consecutive rows. There are ranges like F4 to F10, then F13 to f16, then the
rest are single cell values every 2nd or 3rd row.

"Joel" wrote:

Try this code.
1) Chage rows of source as necessary F4:F53
2) Adjust the column of the destination as needed = Range("A"

LastRow = Sheets("Results"). _
Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Questionnaire").Range("F4:F53").Copy
Range("A" & (LastRow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

"MLK" wrote:

Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou

I think your #3 response will work great .... really condenses the code.
Thanks again for your help! Mary-Lou

"Joel" wrote:

You have 3 choices
1) Copy then blanks row
2) Change SkipBlanks:=True. This may be a problem if some forms have blanks
in some entries. The data will not allign on the new worksheet.
3) Do the copy in smaller pieces. Or change the range to be pieces
Sheets("Questionnaire").Range("F4:F10","F13:F16"," F20","F27").Copy

"MLK" wrote:

Hi, the Quesitonnaire worksheet does not store the responses in 50
consecutive rows. There are ranges like F4 to F10, then F13 to f16, then the
rest are single cell values every 2nd or 3rd row.

"Joel" wrote:

Try this code.
1) Chage rows of source as necessary F4:F53
2) Adjust the column of the destination as needed = Range("A"

LastRow = Sheets("Results"). _
Cells(Rows.Count, "A").End(xlUp).Row

Sheets("Questionnaire").Range("F4:F53").Copy
Range("A" & (LastRow + 1)).Select
Selection.PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

"MLK" wrote:

Note: I am a beginner at VB.

I have 2 worksheets. The first worksheet "Questionnaire" contains approx 50
questions users fill out (each question is on a separate row - 50 rows) - the
responses go into column F. The second worksheet "Results" is where I want
to consolidate the questionnaire results.

Next, I want to take the responses from column F in the Questinnaire
worksheet and copy the data into the next available row on the Results
worksheet. Therefore taking 50 cells of data (from column F) and putting
them into one row in the Results worksheet (50 columns across).

I thought I could do this a simple way by giving each cell in column F a
Name and simply moving the Named field into the rows in the Results
worksheet... didn't work. I gave each data entry cell a name like :
Value_One, Value_Two up to Value_Fifty.

Here's a sample of what I tried to do for 4 values.


ActiveWorkbook.Sheets("Results").Activate
Range("A5").Select

'In column A, look for next empty cell
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

'move the contents of Application Info to results worksheet
ActiveCell.Value = Value_One
ActiveCell.Offset(0, 1) = Value_Two
ActiveCell.Offset(0, 2) = Value_Three
ActiveCell.Offset(0, 3) = Value_Four


Note: Users may need to fill out the questionnaire more than once, so I
have a button to clear out their responses in order for them to fill it out
again. I also have a button they click when they have completed the
questionnaire - which triggers the Results worksheet to be populated (well it
would do this if it worked).

Is there a shortcut to doing this? If not, how should I be coding this.

Thanks, Mary-Lou