Selct and edit sheet

I am a bit confused about this as I have never had this happen before:

I have a command button on sheet 1 which is basically for validation of two fields of data.
Once the validation check gets through I want to copy the two fields into Sheet 2. But in my code I am selecting sheet 2 but once there and changes I try to make are happening in sheet 1!!

here is the code:

Private Sub Register_Click()

Dim SName As String
Dim Username As String
Dim Session As String

Dim MyTime
MyTime = Time

If MyTime < "12:00:00" Then
Session = "Morning"
ElseIf MyTime < "17:00:00" Then
Session = "Afternoon"
ElseIf MyTime < "23:59:59" Then
Session = "Evening"
Else
End If


SName = Range("f9")
Username = Range("f11")

If SName = "" Then
MsgBox "Please enter your Surname"
ElseIf Username = "" Then
MsgBox "Please enter your username"
Else

Sheets("Data").Select ' HERE I SELECT THE SHEET I WANT TO COPY TO

Range("a2") = SName ' THIS APPEARS IN SHEET 1 !!!
Range("b2") = Username ' THIS ALSO APPEARS IN SHEET 1 !!!!


End If


Do I need to make the second sheet active before I can copy the data?

You have unqualified ranges in your code.

If you use this kind of code in a general module:
Range("a2") = SName
then that A2 will refer to the activesheet.

But if you use that same line of code behind a worksheet, that range will refer
to the worksheet that owns the code.

But if you qualify the range:

Worksheets("data").Range("a2") = SName
worksheets("data").Range("b2") = Username

You'll be fine--since you qualified that range to belong to worksheets("data").

And you'll notice that you can drop the .select statement, too. You can just
drop this line:

Sheets("Data").Select





NNothard wrote:
>
> I am a bit confused about this as I have never had this happen before:
>
> I have a command button on sheet 1 which is basically for validation of
> two fields of data.
> Once the validation check gets through I want to copy the two fields
> into Sheet 2. But in my code I am selecting sheet 2 but once there and
> changes I try to make are happening in sheet 1!!
>
> here is the code:
>
> Private Sub Register_Click()
>
> Dim SName As String
> Dim Username As String
> Dim Session As String
>
> Dim MyTime
> MyTime = Time
>
> If MyTime < "12:00:00" Then
> Session = "Morning"
> ElseIf MyTime < "17:00:00" Then
> Session = "Afternoon"
> ElseIf MyTime < "23:59:59" Then
> Session = "Evening"
> Else
> End If
>
> SName = Range("f9")
> Username = Range("f11")
>
> If SName = "" Then
> MsgBox "Please enter your Surname"
> ElseIf Username = "" Then
> MsgBox "Please enter your username"
> Else
>
> Sheets("Data").Select ' HERE I SELECT THE SHEET I WANT TO COPY TO
>
> Range("a2") = SName ' THIS APPEARS IN SHEET 1 !!!
> Range("b2") = Username ' THIS ALSO APPEARS IN SHEET 1 !!!!
>
> End If
>
> Do I need to make the second sheet active before I can copy the data?
>
> --
> NNothard
> ------------------------------------------------------------------------
> NNothard's Profile: http://www.excelforum.com/member.php...o&userid=35791
> View this thread: http://www.excelforum.com/showthread...hreadid=571431

--

Dave Peterson

Thank-you

It is working now.

I want to shift the row down in the sheet data before inserting the info

the macro code looks like this

Rows("2:2").Select
Selection.Insert Shift:=xlDown

Do I need to do the same as the previous:

Worksheets("data").Rows("2:2") ... ?

Cheers