Macro enable shared file mystery

[WIGG77]

Just a quickie. I know there's a million threads on shared files and they're generally frowned upon as being unreliable.

I use one at work with multiple users and it's relatively stable.
I have a lot of VB in it and all works perfectly when shared.

Just added the following and it works perfectly when unshared but not when shared.
Does anyone know why or if there another solution?

You basically select a cell, message box asks a few question, copies some info to another sheet and deletes the line from the original.
Nothing complicated.

Thanks
Dave

Sub INVOICE()

Dim ANS As Range
Dim lastRow As Long
Dim AnswerYes As String
Dim AnswerNo As String
   
On Error GoTo CANCELED
Set ANS = Application.InputBox("Which Chair do you wish to report as shipped/invoiced?" & vbNewLine & "Please select chair serial number ", "Shipped/Invoiced", Type:=8)
If MsgBox("You are about to report " & ANS & " as shipped/invoiced. Are you sure?", vbYesNo) = vbNo Then Exit Sub

AnswerYes = MsgBox("Has this chair left the building or will be leaving today?" & vbNewLine & vbNewLine & "Select YES if the chair has been or is being despatched today." & vbNewLine
& vbNewLine & "Select NO if Ex-Works and is not being collected today.", vbQuestion + vbYesNo, "EX-WORKS CHECK")

 If AnswerYes = vbYes Then
 ActiveSheet.Range(ANS.Offset(, 0), ANS.Offset(, 17)).Select
Selection.Copy
   Sheets("SHIPPED").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
lastRow = Sheets("SHIPPED").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range(ANS.Offset(0, -1), ANS.Offset(0, 17)).Delete xlShiftUp
Sheets("SHIPPED").Range("R" & lastRow).Value = Date
Sheets("SHIPPED").Range("R" & lastRow).Format = "dd/mm/yy"

 Else
 
 ActiveSheet.Range(ANS.Offset(, 0), ANS.Offset(, 17)).Select
Selection.Copy
   Sheets("SHIPPED").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
lastRow = Sheets("SHIPPED").Range("A" & Rows.Count).End(xlUp).Row
ActiveSheet.Range(ANS.Offset(0, -1), ANS.Offset(0, 17)).Delete xlShiftUp

 End If
Sheets("FINISHED").Select
Range("A1").Select

CANCELED:

End Sub

 

[RoryA]

You cannot insert or delete blocks of cells in a shared workbook.

 

[WIGG77]

OK. Thanks Rory