copying data from multiple worksheets

[Guest]

I have data in the same area (E8..E57)on multiple (22) worksheets. I need to
copy these 22 columns into one worksheet displaying each column separately.
Now I can copy the first column into the destination worksheet, but can I
copy that column so that worksheet reference changes only. ( I would make
the cells absolute references)

 

[Guest]

Here's one way to deliver it in the summary sheet,
dynamic to the source range in each of the 22 source sheets

In the summary sheet,
List the actual 22 source sheetnames in B1 across to W1

Then place in B2:
=INDEX(INDIRECT("'"&B$1&"'!E8:E58"),ROWS($1:1))
Copy B2 across to W2, fill down to W52 to populate.
Cols B to W will return the required results.