Issue with locking a sheet with macro and table

[skf786]

Hello,

im running a macro to paste additional data to a table. So data grows by one row each time the macro is run. in trying to lock the sheet but have kept the range of the table 'unlocked' however the macro does not perform properly and when it is run the new data is pasted outside the table.

here is the macro being used.

any suggestions to fic this would be highly appreciate.

Sub Create_Segment()
'
' Create_Segment Macro
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Range("D29:K29").Select
    Selection.Copy
    Range("Table13[[#Headers],[Segment Code]]").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E10:E13").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E10").Select
End Sub

 

[jasonb75]

The ranges that are locked / unlocked will be set as specific ranges, not to the current size of the table.

You will need to unlock the sheet for the code to run correctly, you can do this at the start of the code then re-protect at the end.

Worksheet.Unprotect method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[skf786]

The ranges that are locked / unlocked will be set as specific ranges, not to the current size of the table.

You will need to unlock the sheet for the code to run correctly, you can do this at the start of the code then re-protect at the end.

Worksheet.Unprotect method (Excel)

Office VBA reference topic

docs.microsoft.com

thanks Jason, will try it out.

regards,

 

[skf786]

thanks Jason, will try it out.

regards,

unprotecting the sheet before and protecting again at the end of the macro worked perfectly. Thank you