Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering data from sheet1 to sheet2
Dear all, I need help in transfering some cells value from one sheet to another. For example: In sheet1, I will put data in cells [D10-D13] and values from these 4 cells will be transfered in row #12 like A12=D10; B12=D11; C12=D13;D12=D14 of sheet2. That means A12=100;B12=234;C12=456;D12=446 IN SHEET2. I am writing these codes in right_click function so after putting all the values user will right click to ensure that all values are given and then I will write those values in sheet2. Then, in Sheet2 the row # will be incremented to 13 and so on. The input cells in SHEET1 will be the same that is D10,D11,D12,D13. Hope I made myself clear. Thanks in advance. Sheet1 Sheet2 C D A B C D 10 1 100 After right click--row#12 100 234 456 446 11 2 234 12 3 456 13 4 446 -- redhat ------------------------------------------------------------------------ redhat's Profile: http://www.excelforum.com/member.php...o&userid=29301 View this thread: http://www.excelforum.com/showthread...hreadid=490172 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Transfering data from sheet1 to sheet2
Place the following in the ThisWorkbook window
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim CopyRng As Range Dim PasteRng As Range Application.ScreenUpdating = False Set CopyRng = Sheets("Sheet1").Range("D10:D13") Set PasteRng = Sheets("Sheet2").Range("A12") CopyRng.Copy If PasteRng.Value = "" Then PasteRng.PasteSpecial , , , Transpose:=True Else: Sheets("Sheet2").Range("A65536").End(xlUp).Offset( 1, 0). _ PasteSpecial , , , Transpose:=True End If CopyRng.ClearContents Application.CutCopyMode = False Application.ScreenUpdating = True End Sub The only problem I see with this is that everytime you right-click, for anything at all and in any sheet, it will paste the information again. I personally don't know how to fix that using the beforesheetrightclick event. I would instead have a button for the user to click to enter the information on the next sheet. If you want to do that, just copy the code to a module and give it a different name (i.e. bound it with Sub copy() end Sub) hope that helps "redhat" wrote: Dear all, I need help in transfering some cells value from one sheet to another. For example: In sheet1, I will put data in cells [D10-D13] and values from these 4 cells will be transfered in row #12 like A12=D10; B12=D11; C12=D13;D12=D14 of sheet2. That means A12=100;B12=234;C12=456;D12=446 IN SHEET2. I am writing these codes in right_click function so after putting all the values user will right click to ensure that all values are given and then I will write those values in sheet2. Then, in Sheet2 the row # will be incremented to 13 and so on. The input cells in SHEET1 will be the same that is D10,D11,D12,D13. Hope I made myself clear. Thanks in advance. Sheet1 Sheet2 C D A B C D 10 1 100 After right click--row#12 100 234 456 446 11 2 234 12 3 456 13 4 446 -- redhat ------------------------------------------------------------------------ redhat's Profile: http://www.excelforum.com/member.php...o&userid=29301 View this thread: http://www.excelforum.com/showthread...hreadid=490172 |
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 | |||
Write Data from Sheet1 to sheet2 Until | Excel Programming |