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

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

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

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