I've done something stupid.

I've created and filled in about 30 questionnaire sheets, each on a seperate
identical worksheet. The questions run down the sheets. The easiest way I
can think of to analyse the results is to compile them on one page, one row
for every sheet (with questions along the top) and then use the auto filter.

Here's the tricky bit. I can't find a way to transpose the cell contents
from downwards on each sheet to across on the summary page. I know I can do
it cell by cell but I don't think I'll live that long.

It'll be too late to transpose each sheet by hand now - too much legwork.

Copy the cells, the click the little "down arrow" beside the paste
button and choose transpose.
Or by code:
Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
If ws.name < "Summary" Then
with ws
..Range("A1",.Cells(.Range("A1").End(xlDown).Row,. Range("A1").End(xlToRight).Column).Copy
End With
With ActiveWorkbook.Sheets("Summary")
..Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End If
Next

HTH

Charles Chickering
merlin wrote:
I've done something stupid.

I've created and filled in about 30 questionnaire sheets, each on a seperate
identical worksheet. The questions run down the sheets. The easiest way I
can think of to analyse the results is to compile them on one page, one row
for every sheet (with questions along the top) and then use the auto filter.

Here's the tricky bit. I can't find a way to transpose the cell contents
from downwards on each sheet to across on the summary page. I know I can do
it cell by cell but I don't think I'll live that long.

It'll be too late to transpose each sheet by hand now - too much legwork.

Is there any way I can do it with auto update? (VBA code is beyond me at the
moment I'm afraid).

It seems like it should be a simple thing to copy a column from each of
several sheets to the next row on a summary sheet?

How about this as the next best thing? I can precede each row on the summary
sheet with the sheet's name. Then if I manually copy the absolute cell
reference for each item in the column going down on the first questionnaire
to each cell in the first row going across of my summary sheet, can I then
replace the sheet reference to the first cell in that row on the summary
sheet that contains the sheet name for that row? Then if I drag the row
downwards it should auto update for every sheet on each row. Hope that's
clearish....

I've no idea of the syntax to achieve that but it would suit me if it's
possible.



"Die_Another_Day" wrote in message
ups.com...
Copy the cells, the click the little "down arrow" beside the paste
button and choose transpose.
Or by code:
Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
If ws.name < "Summary" Then
with ws
.Range("A1",.Cells(.Range("A1").End(xlDown).Row,.R ange("A1").End(xlToRight).Column).Copy
End With
With ActiveWorkbook.Sheets("Summary")
.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End If
Next

HTH

Charles Chickering
merlin wrote:
I've done something stupid.

I've created and filled in about 30 questionnaire sheets, each on a
seperate
identical worksheet. The questions run down the sheets. The easiest way I
can think of to analyse the results is to compile them on one page, one
row
for every sheet (with questions along the top) and then use the auto
filter.

Here's the tricky bit. I can't find a way to transpose the cell contents
from downwards on each sheet to across on the summary page. I know I can
do
it cell by cell but I don't think I'll live that long.

It'll be too late to transpose each sheet by hand now - too much legwork.

Try this then:
=INDIRECT($A1 & "!A" & COLUMN() - 1)
Cell A1 needs to hold the sheet name Change the A in !A to the column
that you want...

Charles

merlin wrote:
Is there any way I can do it with auto update? (VBA code is beyond me at the
moment I'm afraid).

It seems like it should be a simple thing to copy a column from each of
several sheets to the next row on a summary sheet?

How about this as the next best thing? I can precede each row on the summary
sheet with the sheet's name. Then if I manually copy the absolute cell
reference for each item in the column going down on the first questionnaire
to each cell in the first row going across of my summary sheet, can I then
replace the sheet reference to the first cell in that row on the summary
sheet that contains the sheet name for that row? Then if I drag the row
downwards it should auto update for every sheet on each row. Hope that's
clearish....

I've no idea of the syntax to achieve that but it would suit me if it's
possible.



"Die_Another_Day" wrote in message
ups.com...
Copy the cells, the click the little "down arrow" beside the paste
button and choose transpose.
Or by code:
Dim ws as Worksheet
For Each ws in ActiveWorkbook.Worksheets
If ws.name < "Summary" Then
with ws
.Range("A1",.Cells(.Range("A1").End(xlDown).Row,.R ange("A1").End(xlToRight).Column).Copy
End With
With ActiveWorkbook.Sheets("Summary")
.Range("A" & .Rows.Count).End(xlUp).Offset(1).PasteSpecial _
Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End With
End If
Next

HTH

Charles Chickering
merlin wrote:
I've done something stupid.

I've created and filled in about 30 questionnaire sheets, each on a
seperate
identical worksheet. The questions run down the sheets. The easiest way I
can think of to analyse the results is to compile them on one page, one
row
for every sheet (with questions along the top) and then use the auto
filter.

Here's the tricky bit. I can't find a way to transpose the cell contents
from downwards on each sheet to across on the summary page. I know I can
do
it cell by cell but I don't think I'll live that long.

It'll be too late to transpose each sheet by hand now - too much legwork.