Insert cut row in protected range of rows
I need help.
I need a VBA code for a worksheet to automatically insert cut paste a row from a range of unprotected rows to the range of protected rows once the user decides to do so.
I have a sheet called “Customer Stock” which has two tables called “Uncollected” and “Collected”.
I need “Uncollected” table to be unprotected and “Collected” Table to be protected.
Once the a Customer collects the goods, the user can cut and insert the collected row from the unprotected “uncollected” table to just above the first row of the protected “collected” table.
so that the inserted cut row now should be protected.
The password should be 1234 and the user won’t know the password. Only the administrator will be having it.
| Attachment | Size |
|---|---|
| Daily Stock.xlsx | 309.61 KB |
Not neat but here you go
There is possibly a better way to do this but if the person has the cell highlighted in column A for the correct row to move then the following VBA should work as part of a Macro:
Sub Macro1()
'
' Macro1 Macro
'
'
ActiveSheet.Unprotect
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Cut
Range("A191").Select
Selection.Insert Shift:=xlDown
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 14803455
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Selection.Locked = True
Selection.FormulaHidden = False
Range("A1").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
Regards
Mark