Maperalia,

I'm sorry I can't post the sample codes here as they're already quite
long.

I've attached a zip file (Transpose.zip) containg an excel file and a
text file.

The excel file contains the codes that hopefully may give you answers.
Feel free to examine the codes in the file.

Please read the text file first for info.

Hope it helps... :)

maperalia Wrote:
T-Ex;
Thanks for you code. It is running properly!!!!.
Tell me I have tried to use your code you invert the transpose but I
was
unsuccefull; for example :
If I enter in Sheet1:
A1 - "the"
A2 - "quick"
A3 - "brown"
A4 - "fox"

and Sheet2 column A is still empty, should the result be:
A1 - "the"
B1 - "quick"
C1 - "brown"
D1 - "fox"

???

But when I enter in Sheet1:
A1 - "jumps"
A2 - "over"
A3 - "the"
A4 - "candle stick"

and Sheet2 column A already has data, should the result be written in
column B?
A2 - "jumps"
B2 - "over"
C2 - "the"
D2 - "candle stick

I sorry to bother you again but I am looking both options.

Thanks for your patience and I really appreciate your taking you time
to
help me.

Best regards
Maperalia





"T-®ex" wrote:


Hi Maperalia! Try the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
TransposeTo Target, Sheet2
End Sub

Sub TransposeTo(ByVal Target As Range, ByVal DestSheet As Worksheet)
If Target.Worksheet.CodeName < DestSheet.CodeName Then
DestSheet.Cells(Target.Column,
NextColumn(DestSheet.Cells(Target.Column, Target.Row))).Value =
Target.Value
End If
End Sub

'returns the column number of the first empty column to the right of
Target
Function NextColumn(ByVal Target As Range) As Integer
Dim ColNum As Integer
ColNum = Target.Column

Dim NextCol As Range
Set NextCol = Target

While Not IsEmpty(NextCol.Value)
Set NextCol = NextCol.Offset(0, 1)
Wend

NextColumn = NextCol.Column
End Function

'determines whether a column (TargetCol) is empty
Function IsColumnEmpty(ByVal TargetCol As Range) As Boolean
Dim EntireCol As Range 'just making sure we really do have the
entire column
Set EntireCol = TargetCol.EntireColumn

Dim ColEmpty As Boolean
ColEmpty = True

If Not IsEmpty(TargetCol.Item(1)) Then
ColEmpty = False
ElseIf Not IsEmpty(EntireCol.Rows.Item(EntireCol.Rows.Count)) Then
ColEmpty = False
Else
Dim LastCell As Range
Set LastCell = EntireCol.End(xlDown)

If LastCell.Row < 65536 Then
ColEmpty = False
End If
End If

IsColumnEmpty = ColEmpty
End Function

I'm assuming you're still using Sheet1 as input sheet and Sheet2 as
result sheet.
In this case, put the code above in Sheet1. (The code is not very
elegant... sorry...)


--
T-®ex

------------------------------------------------------------------------
T-®ex's Profile:
http://www.excelforum.com/member.php...o&userid=26572
View this thread:
http://www.excelforum.com/showthread...hreadid=399965




+-------------------------------------------------------------------+
|Filename: Transpose.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3767 |
+-------------------------------------------------------------------+

--
T-®ex
------------------------------------------------------------------------
T-®ex's Profile: http://www.excelforum.com/member.php...o&userid=26572
View this thread: http://www.excelforum.com/showthread...hreadid=399965