If someone would be kind enough to help me, I would greatly appreciate
it.


I simply want to take a row of answers from 10 students and position
these answers horizontally instead of vertically on another
spreadsheet. Seems simple enough but for the life of me, I can not
figure it out.

So Student One's answers are presently listed like the following:

1
2
3
1
4
2
3
3

I would like to list on another spreadsheet like this:

Student One 12314233

Any help would be appreciated.

I have the following code, but it does not seem to work:

Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))
X = 1
Y = 1

ReDim Data(Source.Count)


LastColumn = ActiveSheet.UsedRange.Columns
(ActiveSheet.UsedRange.Columns.Count).Column

LastRow = ActiveSheet.UsedRange.Rows
(ActiveSheet.UsedRange.Rows.Count).Row


For Each K In Source

Data(X) = K.Value

X = X + 1
Next
Sheets(1).Select
Range("b3").Select

rw = 2
cl = 10

For cl = 10 To LastColumn

For rw = 2 To LastRow

Cells(rw, cl).Value = Data(rw)

X = X + 1




Next rw

Next cl


End Sub

Hi

You are really complicating things to much....

Copy the range and use PasteSpecial = Transpose, in VBA someting like
this:

Sub aaa()
LastColumn = ActiveSheet.UsedRange.Columns
LastRow = ActiveSheet.UsedRange.Rows
Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))

Source.Copy
Sheets(1).Cells(3, 10).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Regards,
Per



On 29 Sep., 03:39, Rob wrote:
If someone would be kind enough to help me, I would greatly appreciate
it.

I simply want to take a row of answers from 10 students and position
these answers horizontally instead of vertically on another
spreadsheet. Seems simple enough but for the life of me, I can not
figure it out.

So Student One's answers are presently listed like the following:

1
2
3
1
4
2
3
3

I would like to list on another spreadsheet like this:

Student One 12314233

Any help would be appreciated.

I have the following code, but it does not seem to work:

*Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))
*X = 1
*Y = 1

*ReDim Data(Source.Count)

*LastColumn = ActiveSheet.UsedRange.Columns
(ActiveSheet.UsedRange.Columns.Count).Column

*LastRow = ActiveSheet.UsedRange.Rows
(ActiveSheet.UsedRange.Rows.Count).Row

*For Each K In Source

* *Data(X) = K.Value

* *X = X + 1
*Next
* *Sheets(1).Select
* *Range("b3").Select

*rw = 2
*cl = 10

*For cl = 10 To LastColumn

* *For rw = 2 To LastRow

* * * * * * * *Cells(rw, cl).Value = Data(rw)

* * * * * * * *X = X + 1

* *Next rw

* *Next cl

End Sub

I think it can be simplified even more. First though, your LastColumn and
LastRow assignment statements are missing a .Count (dot-count) property call
at the end of each statement. However, I'm not entirely sure I would be
comfortable using the UsedRange to produce the ends of the data the OP wants
to transpose (I know you were just using what he posted, I just don't think
he was on the right track there). So, in my code, I assumed he actually
wanted the CurrentRegion range instead (although any range specification can
be used as necessary). Most of the code just sets things up... the workhorse
line is the single line at the end.

Sub TransposeRangeToAnotherSheet()

Dim Source As Range, Destination As Range
Const DestinationSheet = 2
Const DestinationStartCell = "D5"

Set Source = Cells(3, 10).CurrentRegion
Set Destination = Sheets(DestinationSheet).Range(DestinationStartCel l)

Destination.Resize(Source.Columns.Count, Source.Rows.Count) = _
WorksheetFunction.Transpose(Source)

End Sub

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
Hi

You are really complicating things to much....

Copy the range and use PasteSpecial = Transpose, in VBA someting like
this:

Sub aaa()
LastColumn = ActiveSheet.UsedRange.Columns
LastRow = ActiveSheet.UsedRange.Rows
Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))

Source.Copy
Sheets(1).Cells(3, 10).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Regards,
Per



On 29 Sep., 03:39, Rob wrote:
If someone would be kind enough to help me, I would greatly appreciate
it.

I simply want to take a row of answers from 10 students and position
these answers horizontally instead of vertically on another
spreadsheet. Seems simple enough but for the life of me, I can not
figure it out.

So Student One's answers are presently listed like the following:

1
2
3
1
4
2
3
3

I would like to list on another spreadsheet like this:

Student One 12314233

Any help would be appreciated.

I have the following code, but it does not seem to work:

Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))
X = 1
Y = 1

ReDim Data(Source.Count)

LastColumn = ActiveSheet.UsedRange.Columns
(ActiveSheet.UsedRange.Columns.Count).Column

LastRow = ActiveSheet.UsedRange.Rows
(ActiveSheet.UsedRange.Rows.Count).Row

For Each K In Source

Data(X) = K.Value

X = X + 1
Next
Sheets(1).Select
Range("b3").Select

rw = 2
cl = 10

For cl = 10 To LastColumn

For rw = 2 To LastRow

Cells(rw, cl).Value = Data(rw)

X = X + 1

Next rw

Next cl

End Sub

I should point out (before someone tries to "correct" it thinking I made a
mistake), the use of the source's column count in the destination's Resize
property's Row argument position and the source's row count in the
destination's Resize property's Column argument position are intentional.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I think it can be simplified even more. First though, your LastColumn and
LastRow assignment statements are missing a .Count (dot-count) property
call at the end of each statement. However, I'm not entirely sure I would
be comfortable using the UsedRange to produce the ends of the data the OP
wants to transpose (I know you were just using what he posted, I just don't
think he was on the right track there). So, in my code, I assumed he
actually wanted the CurrentRegion range instead (although any range
specification can be used as necessary). Most of the code just sets things
up... the workhorse line is the single line at the end.

Sub TransposeRangeToAnotherSheet()

Dim Source As Range, Destination As Range
Const DestinationSheet = 2
Const DestinationStartCell = "D5"

Set Source = Cells(3, 10).CurrentRegion
Set Destination = Sheets(DestinationSheet).Range(DestinationStartCel l)

Destination.Resize(Source.Columns.Count, Source.Rows.Count) = _
WorksheetFunction.Transpose(Source)

End Sub

--
Rick (MVP - Excel)


"Per Jessen" wrote in message
...
Hi

You are really complicating things to much....

Copy the range and use PasteSpecial = Transpose, in VBA someting like
this:

Sub aaa()
LastColumn = ActiveSheet.UsedRange.Columns
LastRow = ActiveSheet.UsedRange.Rows
Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))

Source.Copy
Sheets(1).Cells(3, 10).PasteSpecial Transpose:=True
Application.CutCopyMode = False
End Sub

Regards,
Per



On 29 Sep., 03:39, Rob wrote:
If someone would be kind enough to help me, I would greatly appreciate
it.

I simply want to take a row of answers from 10 students and position
these answers horizontally instead of vertically on another
spreadsheet. Seems simple enough but for the life of me, I can not
figure it out.

So Student One's answers are presently listed like the following:

1
2
3
1
4
2
3
3

I would like to list on another spreadsheet like this:

Student One 12314233

Any help would be appreciated.

I have the following code, but it does not seem to work:

Set Source = Range(Cells(3, 10), Cells((LastRow + 1), LastColumn))
X = 1
Y = 1

ReDim Data(Source.Count)

LastColumn = ActiveSheet.UsedRange.Columns
(ActiveSheet.UsedRange.Columns.Count).Column

LastRow = ActiveSheet.UsedRange.Rows
(ActiveSheet.UsedRange.Rows.Count).Row

For Each K In Source

Data(X) = K.Value

X = X + 1
Next
Sheets(1).Select
Range("b3").Select

rw = 2
cl = 10

For cl = 10 To LastColumn

For rw = 2 To LastRow

Cells(rw, cl).Value = Data(rw)

X = X + 1

Next rw

Next cl

End Sub