| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Write Data from Sheet1 to sheet2 Until
How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
You did not indicate where to copy to. Meanwhile, how is this?
Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
How the code will be written if the data from sheet1 is located in the
following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
You can use transpose, looping. Generally you use
sheets(1).range("A1").copy sheets(2).range("A1") change "A" to suit "maperalia" wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
PY;
I have been doing the step you mentioned. However, I have to do it every time I entry the data in the sheet1 then copy transpose into sheet2. I wonder if I can be made automatically. So the data will be typed in the next empty row. Could you please tell me how to do it. Thanks in advance. Maperalia "PY & Associates" wrote: You can use transpose, looping. Generally you use sheets(1).range("A1").copy sheets(2).range("A1") change "A" to suit "maperalia" wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
PY;
I sorry I am in the process of learning VBA. Unfortunately, I do not know how to do the VBA program to automate copy from sheet1 (A1,B1,C1,D1) to sheet2 (A1,A2,A3.A4). I have been doing this manually...... Regards. Maperalia "PY & Associates" wrote: I do not fully understand your problem. You type in sheet1.row1, it appears in sheet2.column1 then you type in sheet1.row2, it appears in sheet2.column? or what is your expectation? Please elaborate. "maperalia" wrote: PY; I have been doing the step you mentioned. However, I have to do it every time I entry the data in the sheet1 then copy transpose into sheet2. I wonder if I can be made automatically. So the data will be typed in the next empty row. Could you please tell me how to do it. Thanks in advance. Maperalia "PY & Associates" wrote: You can use transpose, looping. Generally you use sheets(1).range("A1").copy sheets(2).range("A1") change "A" to suit "maperalia" wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
I do not fully understand your problem.
You type in sheet1.row1, it appears in sheet2.column1 then you type in sheet1.row2, it appears in sheet2.column? or what is your expectation? Please elaborate. "maperalia" wrote: PY; I have been doing the step you mentioned. However, I have to do it every time I entry the data in the sheet1 then copy transpose into sheet2. I wonder if I can be made automatically. So the data will be typed in the next empty row. Could you please tell me how to do it. Thanks in advance. Maperalia "PY & Associates" wrote: You can use transpose, looping. Generally you use sheets(1).range("A1").copy sheets(2).range("A1") change "A" to suit "maperalia" wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
This is a repeat question which I do not understand.
Please see my earlier response "maperalia" wrote: PY; I sorry I am in the process of learning VBA. Unfortunately, I do not know how to do the VBA program to automate copy from sheet1 (A1,B1,C1,D1) to sheet2 (A1,A2,A3.A4). I have been doing this manually...... Regards. Maperalia "PY & Associates" wrote: I do not fully understand your problem. You type in sheet1.row1, it appears in sheet2.column1 then you type in sheet1.row2, it appears in sheet2.column? or what is your expectation? Please elaborate. "maperalia" wrote: PY; I have been doing the step you mentioned. However, I have to do it every time I entry the data in the sheet1 then copy transpose into sheet2. I wonder if I can be made automatically. So the data will be typed in the next empty row. Could you please tell me how to do it. Thanks in advance. Maperalia "PY & Associates" wrote: You can use transpose, looping. Generally you use sheets(1).range("A1").copy sheets(2).range("A1") change "A" to suit "maperalia" wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
Hi Maperalia! I'm not very sure about your problem, but, maybe thi excercise will give you ideas... I'm assuming Sheet1 is where you type in your data and Sheet2 is wher you get the "transposed" results. In Sheet1 (VBA editor), put this code: Private Sub Worksheet_Change(ByVal Target As Range) Sheet2.Cells(Target.Column, Target.Row) = Target.Value End Sub The code above will transpose the data to Sheet2. If you enter in Sheet1: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" Sheet2 will have: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" But, (the side-effect) if you enter in Sheet1: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" Sheet2 will have: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" maperalia Wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile http://www.excelforum.com/member.php...o&userid=26738 View this thread http://www.excelforum.com/showthread...hreadid=399965 -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39996 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-ex,
You are right this is exactly what I am looking for. However, when I typed new data in sheet1 it is overwriting it in sheet2. I wonder if the new data can be written in the following empty row of sheet2. I will really appreciate your helping me with this matter. Thanks again for your support Best regards. Maperalia "T-®ex" wrote: Hi Maperalia! I'm not very sure about your problem, but, maybe this excercise will give you ideas... I'm assuming Sheet1 is where you type in your data and Sheet2 is where you get the "transposed" results. In Sheet1 (VBA editor), put this code: Private Sub Worksheet_Change(ByVal Target As Range) Sheet2.Cells(Target.Column, Target.Row) = Target.Value End Sub The code above will transpose the data to Sheet2. If you enter in Sheet1: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" Sheet2 will have: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" But, (the side-effect) if you enter in Sheet1: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" Sheet2 will have: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" maperalia Wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 -- 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
Hi Maperalia! Can you please elaborate more? Maybe you can attach picture about what you would like done. If you enter in Sheet1: A1 - "the" B1 - "quick" C1 - "brown" D1 - "fox" and Sheet2 column A is still empty, should the result be: A1 - "the" A2 - "quick" A3 - "brown" A4 - "fox" ??? But when you enter in Sheet1: A1 - "jumps" B1 - "over" C1 - "the" D1 - "candle stick" and Sheet2 column A already has data, should the result be written i column B? B1 - "jumps" B2 - "over" B3 - "the" B4 - "candle stick" Is this what you like? I'd like to help but I'm a bit confused...  maperalia Wrote: T-ex, You are right this is exactly what I am looking for. However, when typed new data in sheet1 it is overwriting it in sheet2. I wonder if the ne data can be written in the following empty row of sheet2. I will really appreciate your helping me with this matter. Thanks again for your support Best regards. Maperalia "T-®ex" wrote: Hi Maperalia! I'm not very sure about your problem, but, maybe this excercise will give you ideas... I'm assuming Sheet1 is where you type in your data and Sheet2 i where you get the "transposed" results. In Sheet1 (VBA editor), put this code: Private Sub Worksheet_Change(ByVal Target As Range) Sheet2.Cells(Target.Column, Target.Row) = Target.Value End Sub The code above will transpose the data to Sheet2. If you enter in Sheet1: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" Sheet2 will have: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" But, (the side-effect) if you enter in Sheet1: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" Sheet2 will have: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" maperalia Wrote: How the code will be written if the data from sheet1 is located i the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of th sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data fro Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 -- 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 -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39996 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-Ex;
I do apologize for the confusion!!!. The sample you described is exactly what I want. Could you please help me with the code? Thnaks in Advance. Maperalia "T-®ex" wrote: Hi Maperalia! Can you please elaborate more? Maybe you can attach a picture about what you would like done. If you enter in Sheet1: A1 - "the" B1 - "quick" C1 - "brown" D1 - "fox" and Sheet2 column A is still empty, should the result be: A1 - "the" A2 - "quick" A3 - "brown" A4 - "fox" ??? But when you enter in Sheet1: A1 - "jumps" B1 - "over" C1 - "the" D1 - "candle stick" and Sheet2 column A already has data, should the result be written in column B? B1 - "jumps" B2 - "over" B3 - "the" B4 - "candle stick" Is this what you like? I'd like to help but I'm a bit confused... maperalia Wrote: T-ex, You are right this is exactly what I am looking for. However, when I typed new data in sheet1 it is overwriting it in sheet2. I wonder if the new data can be written in the following empty row of sheet2. I will really appreciate your helping me with this matter. Thanks again for your support Best regards. Maperalia "T-®ex" wrote: Hi Maperalia! I'm not very sure about your problem, but, maybe this excercise will give you ideas... I'm assuming Sheet1 is where you type in your data and Sheet2 is where you get the "transposed" results. In Sheet1 (VBA editor), put this code: Private Sub Worksheet_Change(ByVal Target As Range) Sheet2.Cells(Target.Column, Target.Row) = Target.Value End Sub The code above will transpose the data to Sheet2. If you enter in Sheet1: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" Sheet2 will have: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" But, (the side-effect) if you enter in Sheet1: A1 - "hello" A2 - "world" A3 - "hello" A4 - "vba" Sheet2 will have: A1 - "hello" B1 - "world" C1 - "hello" D1 - "vba" maperalia Wrote: How the code will be written if the data from sheet1 is located in the following cells: A1 B1 C1 D1 Then I want this data to written in the following cells of the sheet2: A1,A2,A3,A4 Thanks in advance.... Maperalia i "PY & Associates" wrote: You did not indicate where to copy to. Meanwhile, how is this? Range(ActiveCell, ActiveCell.End(xlDown)).EntireRow.copy sheets(2).range("....) YOu do not need looping "cunning" wrote: How would I go about coding a Loop to read rows of data from Sheet1 then write the data to Sheet2 until an empty row is encountered? Thanks for any assistance. -- cunning ------------------------------------------------------------------------ cunning's Profile: http://www.excelforum.com/member.php...o&userid=26738 View this thread: http://www.excelforum.com/showthread...hreadid=399965 -- 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 -- 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
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 |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-®ex
You are much cleverer than I am in guessing user's mind. One curiosity, in what way we can make use of this transpose please? "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 |
|
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
If you're planning to use the code in other situations, I must warn yo that it is not very neat as it only exchanges the values of the row an column numbers to acheive transposition... Haven't really tested the code well... So maybe you can try it and tel me what it can and cannot do... ;) As you can see in the code, TransposeTo accepts a Range and WorkSheet. Range is the [Cell] you are transposing. WorkSheet is the destinatio sheet. Please also note the IsColumnEmpty function... I didn't use it!! hahaha.... But, I think it works... (I'm not really making any sense here... Just try the code... :) ) PY & Associates Wrote: T-®ex You are much cleverer than I am in guessing user's mind. One curiosity, in what way we can make use of this transpose please? "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 -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39996 |
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
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 |
|
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
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 |
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-ex;
Thank you for the code.But unfortunately I could not open it. After I click the link I got this message: Invalid Attachment specified. If you followed a valid link, please notify the webmaster I e-mail the excelforum regarding this matter. Thanks again T-ex for all you support and time you dedicate to help me. Best regards. Maoeralia. "T-®ex" wrote: 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 |
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-ex;
Could you please e-mail me the file at . I e-mailed you but I got undelivered message. Thanks again Maperalia "T-®ex" wrote: Hi Maperalia! sorry you can't download the attachment... :( dunno why... if you like you can give me your email and i'll send the attachment to you... if you don't want to "advertise"  your email, then you can emailme at . just make sure i'll know it's from you... then i'll send you the file... Anywayz... you can still try the following: (The codes below are a small part from the file attached previously...) In Sheet1, add the ff code (Be sure you also have Sheet2): Code: -------------------- Option Explicit Dim LastRowVal As Long Dim LastColVal As Integer Private Sub Worksheet_Activate() LastRowVal = 1 'Start at Row 1 LastColVal = 1 'Start at Column A End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'Comment one line and uncomment the other line and see the difference between the two subs ColumnToRow Target, Sheet2 ' RowToColumn Target, Sheet2 End Sub Sub ColumnToRow(ByVal Target As Range, ByVal DestSheet As Worksheet) Dim SRow As Long SRow = NextRow(DestSheet.Cells(Target.Column, Target.Row)) If SRow < LastRowVal Then SRow = LastRowVal End If LastRowVal = SRow If Target.Worksheet.CodeName < DestSheet.CodeName Then DestSheet.Cells(SRow, Target.Row) = Target.Value End If End Sub Sub RowToColumn(ByVal Target As Range, ByVal DestSheet As Worksheet) Dim SCol As Long SCol = NextColumn(DestSheet.Cells(Target.Column, Target.Row)) If SCol < LastColVal Then SCol = LastColVal End If LastColVal = SCol If Target.Worksheet.CodeName < DestSheet.CodeName Then DestSheet.Cells(Target.Column, SCol) = Target.Value End If End Sub 'returns the column number of the first empty cell to the right of Target Function NextColumn(ByVal Target As Range) As Integer Dim NextCol As Range Set NextCol = Target While Not IsEmpty(NextCol.Value) Set NextCol = NextCol.Offset(0, 1) Wend NextColumn = NextCol.Column End Function 'returns the row number of the first empty cell below Target Function NextRow(ByVal Target As Range) As Long Dim NextRow_ As Range Set NextRow_ = Target While Not IsEmpty(NextRow_.Value) Set NextRow_ = NextRow_.Offset(1, 0) Wend NextRow = NextRow_.Row End Function -------------------- Hope this helps... :) -- 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 |
|
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
Hi Maperalia!! Already e-mailed you the file. (Fortunately, I didn't delete it...) ; -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39996 |
|
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
T-Ex
Thank you very much for your help!!!!!. I ran the program and it is running perfectly!!!!!!!!! I really appreciate your taking your time to help me......... Best regards. Maperalia "T-®ex" wrote: Hi Maperalia!! Already e-mailed you the file. (Fortunately, I didn't delete it...) ;) -- 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 |
|
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Write Data from Sheet1 to sheet2 Until
Sure!!! glad i was able to help... maperalia Wrote: T-Ex Thank you very much for your help!!!!!. I ran the program and it is running perfectly!!!!!!!!! I really appreciate your taking your time to help me......... Best regards. Maperalia "T-®ex" wrote: Hi Maperalia!! Already e-mailed you the file. (Fortunately, I didn't delete it...) ;) -- 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 -- T-®e ----------------------------------------------------------------------- T-®ex's Profile: http://www.excelforum.com/member.php...fo&userid=2657 View this thread: http://www.excelforum.com/showthread.php?threadid=39996 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to sheet1 data goes to sheet2 automaticaly ? | Excel Worksheet Functions | |||
| copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
| copying data from sheet1 to sheet2 | Excel Worksheet Functions | |||
| How To Retrieve Data from Sheet2 into Sheet1 | Excel Worksheet Functions | |||
| Transfer data from sheet1 to sheet2 | Excel Programming | |||
Linear Mode
