Try this code instead...

Public Sub test()
Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
Lastcolumn = ws.Cells.Find(What:="*", SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column
.Cells(Lastrow, 1).Copy
.Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)). _
PasteSpecial Paste:=xlPasteAll, Transpose:=True
End With
Next ws
On Error GoTo 0
End Sub

--
Rick (MVP - Excel)


"Sheela" wrote in message
...

Thank you very much, Rick.

It is working the way it is expected. but I need to change my code to find
the last column, not just in row 2. Is there a way to find the last
column
in a worksheet in any row?

Thank you again.

Sheela

"Rick Rothstein" wrote:

Here is your original code, modified to use the statement I posted
originally (notice, the only change I made was to remove the ActiveSheet
reference and replace it with ws which is the variable name you used to
iterate through the worksheets).

Public Sub test()
Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long
On Error Resume Next
For Each ws In ActiveWorkbook.Worksheets
With ws
Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
Lastcolumn = ws.Rows(2).Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
.Cells(Lastrow, 1).Copy
.Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn + 1)).
_
PasteSpecial Paste:=xlPasteAll, Transpose:=True
End With
Next ws
On Error GoTo 0
End Sub

--
Rick (MVP - Excel)


"Sheela" wrote in message
...
Thank you all for your responses.

Ryan: I tried to see whether there are any hidden characters or
anything
with the Ctrl+End, there were no hidden characters. the cursor always
stopped
at the last column.

Rick, I used your function and realized that it calculates the
lastcolumn
in
the first sheet, and always using the same number as lastcolumn in all
other
sheets.

somehow the Lastcolumn is not being calculated in each sheet.
Could you see my code in the original post and advise me ?

thank you very much in advance,
Sheela


"Rick Rothstein" wrote:

Are you looking to locate the last displayed value in Row 2 or the
last
cell
in Row 2 with anything in it? That last part refers to formulas that
are
currently displaying the empty string ("")... the uncommented
Lastcolumn
statement finds the last cell with anything in it (a value OR a
formula
even
if that formula is displaying the empty string). If you are after the
column
with the last displayed value in it (even if there cells after it with
formulas currently displaying empty strings), then use this statement
instead...

Lastcolumn = ActiveSheet.Rows(2).Find(What:="*", LookIn:=xlValues, _
SearchOrder:=xlByColumns,
SearchDirection:=xlPrevious).Column

--
Rick (MVP - Excel)


"Sheela" wrote in message
...

I have a macro to find a last column in all the worksheets in a
workbook.
In some sheets it is correctly selecting the last column, but in
some
sheets
it is passing the last column and selecting some other column on way
right
to
it.
I tried to use the following two methods, both are working the same
way.

Could someone figure out the correct way to find the last column?

Public Sub test()

Dim ws As Worksheet
Dim Lastrow, Lastcolumn As Long

On Error Resume Next

For Each ws In ActiveWorkbook.Worksheets

With ws

Lastrow = .Range("A" & Rows.Count).End(xlUp).Row
'Lastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
Lastcolumn = ActiveSheet.Cells(2,
Columns.Count).End(xlToLeft).Column

.Cells(Lastrow, 1).Copy
.Range(.Cells(1, Lastcolumn + 1), .Cells(Lastrow, Lastcolumn +
1)).PasteSpecial Paste:=xlPasteAll, Transpose:=True

End With

Next ws
On Error GoTo 0

End Sub