Use macro(command button) paste special in 2nd sheet

[deen]

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

 

[JLatham]

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]

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

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

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

 

[deen]

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

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

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

 

[JLatham]

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.

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

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

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

 

[JLatham]

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.

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

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

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