Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the data of sheet 1 to sheet 2 with modify?
How can I copy the data of sheet 1 to sheet 2 with modify? For Example: sheet 1: File Num Vendor Code 1________m1 2________m2 original pmmld 3________m3 sheet 2: File Num Vendor Code 1________m1 2________m2 3________m3 From above example, it will NOT copy "original pmmid". Please let me know, thanks. -- accessman2 ------------------------------------------------------------------------ accessman2's Profile: http://www.excelforum.com/member.php...o&userid=27953 View this thread: http://www.excelforum.com/showthread...hreadid=475715 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How can I copy the data of sheet 1 to sheet 2 with modify?
Try this:
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 Maperalia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
copy data of two cells from Sheet 2 into one cell in Sheet 1 | Excel Worksheet Functions | |||
How can i copy data from a tabbed working sheet to a summary sheet | Excel Discussion (Misc queries) | |||
how to copy a cell with formula from sheet 1 (data is all vertical) into sheet 2 | Excel Worksheet Functions | |||
Copy data from sheet 1 to sheet 2 based on day/date | Excel Programming |