Copying cells from one worksheet to another in transposed manner

[abhinav.agarwal]

Hi everyone,

I am working on creating a VB 6.0 application which has excel as backend. Basically I have 3 sheets
1. A location sheet from where I need to pick values from particular range of cells ( say C2: F20). I have different excel sheets for a different locations and I need to pick values from same range of cells.
2. Next I have A detail Summary sheet. This Summary sheet is initially empty. But basicaly I want the values from these cells in range in sheet 1 to be transposed and if possible linked to the detail summary sheet.

for eg. My sheet 1 cells look like
Config1 Config2 Config3
Cost A
Cost B
Cost C

So now I want this range array to be dispayed like
Cost A Cost B Cost C
Config 1
Config2
COnfig3

I did the same thing in excel(but without using VB ) by using Transpose Function in Excel

But now I want to create Transpose formula in the excel sheet no.2 where If I update the data in sheet 1 its automatically automated reflected in sheet 2. So basically I want to set up transpose formula in excel workbook using VB code. Is it possible. Also there is one more thing I wish to do. I have 10 worksheets corresponding to each location. I want to Pick up same range of cells from all the locations and want to display them on sheet2

somthing like
Cost A Cost B Cost C
Loc1 Config 1
Loc1 Config2
Loc1 COnfig3

Cost A Cost B Cost C
Loc2 Config 1
Loc2 Config2
Loc2 COnfig3

Please help me doing this. Its very important for me.