When you add a sheet, it now becomes the active sheet... so when you
use your short hand notation of just cells(1,i), you are most likely
setting the values on the "TS TOTAL" sheet... Have to be careful with
the select and active sheet method... always using the complete call of
worksheet.cells() is a good habit.

I agree using the transpose would be a lot easier and faster if its
applicable here.


NickHK wrote:
If you are trying to set the value of the cell in Worksheets("TS TOTAL"),
then it needs to be on the left of the "=".
But can you not use TRANSPOSE with your range ?
Range("A1:A2") = WorksheetFunction.Transpose(Range("B1:C1"))

NickHK

"T De Villiers"
wrote in message
news:T.De.Villiers.2buy1p_1154429704.7144@excelfor um-nospam.com...

My current code isnt quite working.
I am trying to get A Range of cells from Row 1 in current sheet into
Column A in "TS Total" Worksheet, the actula range differs, depending
on values of firstcol and lastcol.

Here lastcol = 20 and firstcol = 10

Sheets("Weekly").Select
Sheets.Add.Name = "TS TOTAL"

For i = firstcol + 1 To lastcol - 1 Step 1
Cells(1, i) = Worksheets("TS TOTAL").Cells(i, 1)
Next

Many Thanks


--
T De Villiers
------------------------------------------------------------------------
T De Villiers's Profile:
http://www.excelforum.com/member.php...o&userid=26479
View this thread: http://www.excelforum.com/showthread...hreadid=566977