Moving rows to 2nd tab when worksheet is protected

[Guest]

I've got a spreadsheet that has 2 tabs on it. Both tabs are protected, with
the header (top 3 rows) being locked, and the rest is all unlocked. I have a
button on the top of the form that when clicked, it will move the current row
to the 2nd tab, in A4 (the row right under the header). This works fine,
except when the 2nd row is protected. Is there any way to keep the 2nd sheet
protected, but still let it move the row, either to A4, or to the next
available line, whether it be A10, or A30, or whatever? Is there a way to
make it look for the next blank line and move it there? Or is there a way to
let it move to A4 when the sheet is protected? My current code behind the
Move Row button is below:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Copy
Worksheets("Tracking").Activate 'the other sheet
ActiveSheet.Range("A5").Select
ActiveCell.Insert Shift:=xlShiftDown
Worksheets("Western").Activate 'back to the original
ActiveCell.EntireRow.Delete Shift:=xlShiftUp
End Sub

Thanks!
Stacie

 

[Guest]

Hi,
To cut and paste the current row to A4:
Private Sub MoveRow_Click()
ActiveCell.EntireRow.Cut Worksheets("Tracking").Rows(5)
ActiveCell.EntireRow.Delete
End Sub

There is no need of activating the second sheet .
Now to paste to the next available row in stead of A5 (assuming column A is
used to determine which is the next available row), still feasuble in a
single statement:

Private Sub MoveRow_Click()
ActiveCell.EntireRow.Cut
Worksheets("Tracking").Range("A65536").End(xlUp).Offset(1,0)
ActiveCell.EntireRow.Delete
End Sub

I hope this helps,
Sebastienm

 

[Guest]

and one thing i forgot to say about sheet protection.
You can protect a sheet in UserInterface only mode. In this mode, the user
(as usual) cannot edit protected cells, but vba code can ! Very convenient,
huh!?!
The only inconvenient, the mode is not remembered when the book is closed,
so you have to reset it when the book opens in the Workbook_Open sub of the
ThisWorkbook module:
Assuming you password is "mypassword"

Worksheets("Tracking").Protect Password:="mypassword",
UserInterfaceOnly:=True

This option is available in XL2k and above. Not sure about XL97.

Sebastien

 

[Guest]

THANKS!! It all worked like a charm! You're a lifesaver! Thanks so much for
your help!!

Stacie