Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & paste to protected sheet
I am using the macro below, where would I insert the unprotect and protect
statement for the sheet "Transactions", the latter is protected. sub CopyStatement() Dim LastRow As Long CopyStatementMsg = MsgBox("Are you sure you want to Copy Statement to Transactions Sheet?", vbYesNo + vbDefaultButton2) If CopyStatementMsg = vbNo Then End Application.ScreenUpdating = False ActiveSheet.Unprotect ActiveSheet.UsedRange LastRow = Cells.SpecialCells(xlLastCell).Row LastRow = Cells(Cells.Rows.Count, "W").End(xlUp).Row ActiveSheet.Range("$S$5:$W" & LastRow).Copy Sheets("Transactions").Range("A13").PasteSpecial Paste:=xlValues ActiveSheet.Protect Sheets("Transactions").Activate Range("F13").Select Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & paste to protected sheet
Hi,
Avoid this issue by programatically protecting the worksheet and using the ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True You can run this as a stand alone macro or you can incorporate this into a Workbook_Open procedure. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "nc" wrote: I am using the macro below, where would I insert the unprotect and protect statement for the sheet "Transactions", the latter is protected. sub CopyStatement() Dim LastRow As Long CopyStatementMsg = MsgBox("Are you sure you want to Copy Statement to Transactions Sheet?", vbYesNo + vbDefaultButton2) If CopyStatementMsg = vbNo Then End Application.ScreenUpdating = False ActiveSheet.Unprotect ActiveSheet.UsedRange LastRow = Cells.SpecialCells(xlLastCell).Row LastRow = Cells(Cells.Rows.Count, "W").End(xlUp).Row ActiveSheet.Range("$S$5:$W" & LastRow).Copy Sheets("Transactions").Range("A13").PasteSpecial Paste:=xlValues ActiveSheet.Protect Sheets("Transactions").Activate Range("F13").Select Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & paste to protected sheet
Sorry, your answer was not helpful.
Basically I am just protecting the worksheets with formulas to avoid accidentally deleting them. "Shane Devenshire" wrote: Hi, Avoid this issue by programatically protecting the worksheet and using the ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True You can run this as a stand alone macro or you can incorporate this into a Workbook_Open procedure. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "nc" wrote: I am using the macro below, where would I insert the unprotect and protect statement for the sheet "Transactions", the latter is protected. sub CopyStatement() Dim LastRow As Long CopyStatementMsg = MsgBox("Are you sure you want to Copy Statement to Transactions Sheet?", vbYesNo + vbDefaultButton2) If CopyStatementMsg = vbNo Then End Application.ScreenUpdating = False ActiveSheet.Unprotect ActiveSheet.UsedRange LastRow = Cells.SpecialCells(xlLastCell).Row LastRow = Cells(Cells.Rows.Count, "W").End(xlUp).Row ActiveSheet.Range("$S$5:$W" & LastRow).Copy Sheets("Transactions").Range("A13").PasteSpecial Paste:=xlValues ActiveSheet.Protect Sheets("Transactions").Activate Range("F13").Select Application.ScreenUpdating = True End Sub |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Copy & paste to protected sheet
I used
Worksheets("Transactions").Unprotect It worked. "Shane Devenshire" wrote: Hi, Avoid this issue by programatically protecting the worksheet and using the ActiveSheet.Protect Password:="x", UserInterfaceOnly:=True You can run this as a stand alone macro or you can incorporate this into a Workbook_Open procedure. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "nc" wrote: I am using the macro below, where would I insert the unprotect and protect statement for the sheet "Transactions", the latter is protected. sub CopyStatement() Dim LastRow As Long CopyStatementMsg = MsgBox("Are you sure you want to Copy Statement to Transactions Sheet?", vbYesNo + vbDefaultButton2) If CopyStatementMsg = vbNo Then End Application.ScreenUpdating = False ActiveSheet.Unprotect ActiveSheet.UsedRange LastRow = Cells.SpecialCells(xlLastCell).Row LastRow = Cells(Cells.Rows.Count, "W").End(xlUp).Row ActiveSheet.Range("$S$5:$W" & LastRow).Copy Sheets("Transactions").Range("A13").PasteSpecial Paste:=xlValues ActiveSheet.Protect Sheets("Transactions").Activate Range("F13").Select Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy paste Macro in a Protected Sheet | Excel Discussion (Misc queries) | |||
How copy/paste values in Excel doc protected sheet 2 other doc? | Excel Discussion (Misc queries) | |||
Do not allow Copy/Paste Functionality in a Protected Sheet | Excel Worksheet Functions | |||
How to disable copy and paste function in a protected sheet | Setting up and Configuration of Excel | |||
How to copy/paste info into the protected sheet | Excel Discussion (Misc queries) |