Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 ------------------------------------------------------------------------ Allan R Jeffery's Profile: http://www.excelforum.com/member.php...o&userid=37860 View this thread: http://www.excelforum.com/showthread...hreadid=574108 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
code is not to work on sheet1
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
code is not to work on sheet1
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
code is not to work on sheet1
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" <Allan.R.Jeffery.2cxxr5_1156248913.7807@excelfor um-nospam.com wrote in message news:Allan.R.Jeffery.2cxxr5_1156248913.7807@excelf orum-nospam.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! | Excel Discussion (Misc queries) | |||
Can someone help me put psuedo code into actual excel macro?? | Excel Discussion (Misc queries) | |||
t-distribution puzzle in Excel | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Using other workbooks.. | Excel Worksheet Functions |