| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Use macro(command button) paste special in 2nd sheet
Hi every,
How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use macro(command button) paste special in 2nd sheet
deen,
Do you really need a button? This can be done with the _Change() event handler for Sheet1 with the following code. Set up your data validation in cell A1 on Sheet1 and then... To put the code into your workbook in the proper location, choose Sheet1 and right-click on the sheet's name tab and then choose [View Code] from the list that appears. Copy the code below and paste it into the code module that appears. Then close the Visual Basic Editor. Save your workbook. As names are chosen in Sheet1!A1, the person will be asked if they wish to "sign in" at this time or not. If they click [Yes], then the time is placed next to their name and their name and the time is copied into Sheet2. If they click [No], their name is removed from Sheet1!A1 and the time is erased and nothing is copied to Sheet2. Private Sub Worksheet_Change(ByVal Target As Range) 'did change take place in Cell A1 on this sheet? If Application.Intersect(Target, Range("A1")) _ Is Nothing Then 'no Exit Sub End If If Target.Cells.Count 1 Then 'too many cells selected Exit Sub End If If Trim(Target) = "" Then 'they chose [Del] and emptied the cell 'or left it blank Exit Sub End If 'verify the person wants to sign in If MsgBox(Target & _ ", do you wish to sign in now?", _ vbYesNo + vbQuestion, "Sign In Now?") _ < vbYes Then 'no, don't sign in now MsgBox "Thank you. " & _ "You have NOT been signed in at this time." Application.EnableEvents = False Target = "Choose Name" Target.Offset(0, 1) = "" ' clear the time Application.EnableEvents = True Exit Sub End If Target.Offset(0, 1) = Now() ' set the time next to the name 'copy the information to 1st available row on Sheet2 'beginning at row 2 'copy the name Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) = Target 'copy the sign-in time Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(0, 1) = Target.Offset(0, 1) End Sub "deen" wrote: Hi every, How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use macro(command button) paste special in 2nd sheet
Worderful my dear friend, its working fine, in this one i need a small help from end, i need to run this one to my client side , every user should sign in (is must) so in tht place i no need for the no option how to disble the no option, And 1 more thing once the user select the user name , automatically ip address of the computer it present in sheet2 c2 column its possible, friend pls help me out this Regards, deen On Feb 15, 7:03 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: deen, Do you really need a button? This can be done with the _Change() event handler for Sheet1 with the following code. Set up your data validation in cell A1 on Sheet1 and then... To put the code into your workbook in the proper location, choose Sheet1 and right-click on the sheet's name tab and then choose [View Code] from the list that appears. Copy the code below and paste it into the code module that appears. Then close the Visual Basic Editor. Save your workbook. As names are chosen in Sheet1!A1, the person will be asked if they wish to "sign in" at this time or not. If they click [Yes], then the time is placed next to their name and their name and the time is copied into Sheet2. If they click [No], their name is removed from Sheet1!A1 and the time is erased and nothing is copied to Sheet2. Private Sub Worksheet_Change(ByVal Target As Range) 'did change take place in Cell A1 on this sheet? If Application.Intersect(Target, Range("A1")) _ Is Nothing Then 'no Exit Sub End If If Target.Cells.Count 1 Then 'too many cells selected Exit Sub End If If Trim(Target) = "" Then 'they chose [Del] and emptied the cell 'or left it blank Exit Sub End If 'verify the person wants to sign in If MsgBox(Target & _ ", do you wish to sign in now?", _ vbYesNo + vbQuestion, "Sign In Now?") _ < vbYes Then 'no, don't sign in now MsgBox "Thank you. " & _ "You have NOT been signed in at this time." Application.EnableEvents = False Target = "Choose Name" Target.Offset(0, 1) = "" ' clear the time Application.EnableEvents = True Exit Sub End If Target.Offset(0, 1) = Now() ' set the time next to the name 'copy the information to 1st available row on Sheet2 'beginning at row 2 'copy the name Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) = Target 'copy the sign-in time Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(0, 1) = Target.Offset(0, 1) End Sub "deen" wrote: Hi every, How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use macro(command button) paste special in 2nd sheet
Hi Friend,
I have 1 more doubt, In this same sheet1c2, in future planing to add some more formula like , vlookup,if, how i can paste special the data in sheet2 c2....., can you pls help me, Regards, Deen On Feb 15, 7:03 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: deen, Do you really need a button? This can be done with the _Change() event handler for Sheet1 with the following code. Set up your data validation in cell A1 on Sheet1 and then... To put the code into your workbook in the proper location, choose Sheet1 and right-click on the sheet's name tab and then choose [View Code] from the list that appears. Copy the code below and paste it into the code module that appears. Then close the Visual Basic Editor. Save your workbook. As names are chosen in Sheet1!A1, the person will be asked if they wish to "sign in" at this time or not. If they click [Yes], then the time is placed next to their name and their name and the time is copied into Sheet2. If they click [No], their name is removed from Sheet1!A1 and the time is erased and nothing is copied to Sheet2. Private Sub Worksheet_Change(ByVal Target As Range) 'did change take place in Cell A1 on this sheet? If Application.Intersect(Target, Range("A1")) _ Is Nothing Then 'no Exit Sub End If If Target.Cells.Count 1 Then 'too many cells selected Exit Sub End If If Trim(Target) = "" Then 'they chose [Del] and emptied the cell 'or left it blank Exit Sub End If 'verify the person wants to sign in If MsgBox(Target & _ ", do you wish to sign in now?", _ vbYesNo + vbQuestion, "Sign In Now?") _ < vbYes Then 'no, don't sign in now MsgBox "Thank you. " & _ "You have NOT been signed in at this time." Application.EnableEvents = False Target = "Choose Name" Target.Offset(0, 1) = "" ' clear the time Application.EnableEvents = True Exit Sub End If Target.Offset(0, 1) = Now() ' set the time next to the name 'copy the information to 1st available row on Sheet2 'beginning at row 2 'copy the name Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) = Target 'copy the sign-in time Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(0, 1) = Target.Offset(0, 1) End Sub "deen" wrote: Hi every, How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use macro(command button) paste special in 2nd sheet
deen,
Somehow the system missed sending me a notice of your posting of the 17th, and I've just seen your post of the 24th. I think I can help with the requests except for the IP. Excel isn't set up to determine the IP. But we should be able to get the username. I will look at all of this closer this evening and post more after I have done more work with it. "deen" wrote: Hi Friend, I have 1 more doubt, In this same sheet1c2, in future planing to add some more formula like , vlookup,if, how i can paste special the data in sheet2 c2....., can you pls help me, Regards, Deen On Feb 15, 7:03 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: deen, Do you really need a button? This can be done with the _Change() event handler for Sheet1 with the following code. Set up your data validation in cell A1 on Sheet1 and then... To put the code into your workbook in the proper location, choose Sheet1 and right-click on the sheet's name tab and then choose [View Code] from the list that appears. Copy the code below and paste it into the code module that appears. Then close the Visual Basic Editor. Save your workbook. As names are chosen in Sheet1!A1, the person will be asked if they wish to "sign in" at this time or not. If they click [Yes], then the time is placed next to their name and their name and the time is copied into Sheet2. If they click [No], their name is removed from Sheet1!A1 and the time is erased and nothing is copied to Sheet2. Private Sub Worksheet_Change(ByVal Target As Range) 'did change take place in Cell A1 on this sheet? If Application.Intersect(Target, Range("A1")) _ Is Nothing Then 'no Exit Sub End If If Target.Cells.Count 1 Then 'too many cells selected Exit Sub End If If Trim(Target) = "" Then 'they chose [Del] and emptied the cell 'or left it blank Exit Sub End If 'verify the person wants to sign in If MsgBox(Target & _ ", do you wish to sign in now?", _ vbYesNo + vbQuestion, "Sign In Now?") _ < vbYes Then 'no, don't sign in now MsgBox "Thank you. " & _ "You have NOT been signed in at this time." Application.EnableEvents = False Target = "Choose Name" Target.Offset(0, 1) = "" ' clear the time Application.EnableEvents = True Exit Sub End If Target.Offset(0, 1) = Now() ' set the time next to the name 'copy the information to 1st available row on Sheet2 'beginning at row 2 'copy the name Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) = Target 'copy the sign-in time Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(0, 1) = Target.Offset(0, 1) End Sub "deen" wrote: Hi every, How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Use macro(command button) paste special in 2nd sheet
Can you send eMail to me at (remove the spaces from this address)
HelpFrom @ JLathamsite.com There are going to be more questions from me to you, and it will be better to communicate via eMail instead of through this forum. "deen" wrote: Hi Friend, I have 1 more doubt, In this same sheet1c2, in future planing to add some more formula like , vlookup,if, how i can paste special the data in sheet2 c2....., can you pls help me, Regards, Deen On Feb 15, 7:03 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: deen, Do you really need a button? This can be done with the _Change() event handler for Sheet1 with the following code. Set up your data validation in cell A1 on Sheet1 and then... To put the code into your workbook in the proper location, choose Sheet1 and right-click on the sheet's name tab and then choose [View Code] from the list that appears. Copy the code below and paste it into the code module that appears. Then close the Visual Basic Editor. Save your workbook. As names are chosen in Sheet1!A1, the person will be asked if they wish to "sign in" at this time or not. If they click [Yes], then the time is placed next to their name and their name and the time is copied into Sheet2. If they click [No], their name is removed from Sheet1!A1 and the time is erased and nothing is copied to Sheet2. Private Sub Worksheet_Change(ByVal Target As Range) 'did change take place in Cell A1 on this sheet? If Application.Intersect(Target, Range("A1")) _ Is Nothing Then 'no Exit Sub End If If Target.Cells.Count 1 Then 'too many cells selected Exit Sub End If If Trim(Target) = "" Then 'they chose [Del] and emptied the cell 'or left it blank Exit Sub End If 'verify the person wants to sign in If MsgBox(Target & _ ", do you wish to sign in now?", _ vbYesNo + vbQuestion, "Sign In Now?") _ < vbYes Then 'no, don't sign in now MsgBox "Thank you. " & _ "You have NOT been signed in at this time." Application.EnableEvents = False Target = "Choose Name" Target.Offset(0, 1) = "" ' clear the time Application.EnableEvents = True Exit Sub End If Target.Offset(0, 1) = Now() ' set the time next to the name 'copy the information to 1st available row on Sheet2 'beginning at row 2 'copy the name Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(1, 0) = Target 'copy the sign-in time Worksheets("Sheet2").Range("A" & _ Rows.Count).End(xlUp).Offset(0, 1) = Target.Offset(0, 1) End Sub "deen" wrote: Hi every, How are you all, i was facing the problem, in XL i have 2 sheet name sheet1 and sheet2, IN SHEET1 COLUMNA1 I HAVE PEOPLES NAME LIKE JOHN,SEENA,ROCK LIKE THIS WITH VALIDATION LIST(COLUMNA1), COLUMNB1 FORMULA IS THERE =now formula HAS CONSIDER AS TIME AND DATE, IN COLUMNA1 PEOPLE HAVE HIS/HER have SELECT THE NAME LIKE SEENA , ROCK, ONCE SELECT THE NAME CLICK COMMAND BUTTON (SAVE) I NEED TO PASTE SPECIAL IN SHEET2 COLUMNA1 NAME(ROCK) COLUMNB1 TIME , ONCE SEENA GOING TO BE SELECT ABOVE PROCEDURE WILL AUTOMATICALLY PASTE SPECIAL IN COLUMNA2& COLUMNB2 MEAN ITS NEVER GOING TO BE AFFECT THE PREVIOUS RECORD.EXCEL AUTOMATICALLY SAVE CLOSE EG: RESULT I NEED LIKE IN (SHEET2) A B 1 ROCK 15/02/08 8:59AM 2 SEENA 15/02/08 9:00AM 3 JOHN 15/02/08 11:00AM LIKE THIS I HAVE MORE THAN 50 PEOPLES, KINDLY HELP ON THIS, REGARDS |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Push button paste special? | Excel Discussion (Misc queries) | |||
| push button paste special | Excel Discussion (Misc queries) | |||
| Paste and Paste Special command are not enabled in Excel | Excel Worksheet Functions | |||
| How to get "Paste Special" as a button on toolbar | Excel Discussion (Misc queries) | |||
| How do I add a command button on a worksheet to "paste" from the . | Excel Worksheet Functions | |||
Linear Mode
