code is not to work on sheet1

Hello to all.

If possible could someone help here.
This workbook is a bookin sheet for guests.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

' VB TO COPY CONTENTS FROM LAST SHEET TO THIS ONE
If ActiveCell.Row < 11 Then Exit Sub
If ActiveCell.Row > 64 Then Exit Sub
If ActiveCell.Column > 1 Then Exit Sub
ActiveSheet.Unprotect
Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).Copy

ActiveSheet.Paste

end sub

As you can see here, this bit of code copies the row from previous sheet over the active sheet. This also works even when i am on sheet1, however there should be nothing to copy. Is it possible for the this code to look at what sheet it is on and if sheet1 then not execute.

Many thanks Allan

Allan R Jeffery wrote:
> Hello to all.
>
> If possible could someone help here.
> This workbook is a bookin sheet for guests.
>
> Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
> Target As Range)
>
> ' VB TO COPY CONTENTS FROM LAST SHEET TO THIS ONE
> If ActiveCell.Row < 11 Then Exit Sub
> If ActiveCell.Row > 64 Then Exit Sub
> If ActiveCell.Column > 1 Then Exit Sub
> ActiveSheet.Unprotect
> Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).Copy
>
> ActiveSheet.Paste
>
> end sub
>
> As you can see here, this bit of code copies the row from previous
> sheet over the active sheet. This also works even when i am on sheet1,
> however there should be nothing to copy. Is it possible for the this
> code to look at what sheet it is on and if sheet1 then not execute.
>
> Many thanks Allan


Hi Allan,

try this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)

' VB TO COPY CONTENTS FROM LAST SHEET TO THIS ONE

If ActiveSheet.Name = "Sheet1" Then Exit Sub

If ActiveCell.Row < 11 Then Exit Sub
If ActiveCell.Row > 64 Then Exit Sub
If ActiveCell.Column > 1 Then Exit Sub
ActiveSheet.Unprotect
Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).Copy

ActiveSheet.Paste

end sub




--
Hope I helped you.

Thanks in advance for your feedback.

Ciao

Franz Verga from Italy

Thanks Franz

It worked a charm and thanks for the quick reply

Allan

Excel gives you the sh and the activecell, so why not use them?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

' VB TO COPY CONTENTS FROM LAST SHEET TO THIS ONE

If Sh.Name = "Sheet1" Then Exit Sub

If Target.Cells(1, 1).Row < 11 Then Exit Sub
If Target.Cells(1, 1).Row > 64 Then Exit Sub
If Target.Cells(1, 1).Column > 1 Then Exit Sub
ActiveSheet.Unprotect
Sheets(Sh.Index - 1).Rows(Target.Cells(1, 1).Row).Copy

Sh.Paste

End Sub




--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Allan R Jeffery"
<[email protected]> wrote in
message news:[email protected]...
>
> Thanks Franz
>
> It worked a charm and thanks for the quick reply
>
> Allan
>
>
> --
> Allan R Jeffery
> ------------------------------------------------------------------------
> Allan R Jeffery's Profile:
http://www.excelforum.com/member.php...o&userid=37860
> View this thread: http://www.excelforum.com/showthread...hreadid=574108
>

Many thanks Bob

I have changed mine to yours, and all is good.

If Possible you may be able to help with this one, and i think it was you who helped me before with copying data from the previous sheet to the active sheet.

My problem even though it works could do with some cleaning.

Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).copy
ActiveCell.FormulaR1C1 = ""
ActiveSheet.Protect
End
End If
If ActiveCell.FormulaR1C1 = "1" Then
Selection.ClearContents
ActiveCell.FormulaR1C1 = "2"
End
End If
If ActiveCell.FormulaR1C1 = "2" Then
Selection.ClearContents
ActiveCell.FormulaR1C1 = "3"

You can see that this is ugly, very long and it goes up to 60

Can this be re-worded (is that a word ) to eg:

Sheets(ActiveSheet.Index - 1).Rows(ActiveCell.Row).copy

If ActiveCell.FormulaR1C1 = (what ever the cell value is) Then
Selection.ClearContents
ActiveCell.FormulaR1C1 = (cell value +1) since the increment will always only 1
End
any help would be great.

Thank Allan