Solved: Why doesn't this work?

[MarkNumskull]

Hi,

I have written the following code to take a name from a user then delete a worksheet with the relevant name.

After it deletes the sheet i want it to search down a sheet called "data" and then remove the corresponding name for the list.

My loop proecdure works fine on its own but when combined as below it throws up errors namely "400"

[VBA]
Sub Removestaffmember()
Dim Staff As String
Dim Answer As String

Staff = InputBox("Please Enter The Name Of The Staff Member You Want To Remove", " Remove Staff Member")

Answer = MsgBox("Deleting will permanently remove the record, do you want to continue?", vbOKCancel)
Application.DisplayAlerts = False

If Answer <> vbOK Then Exit Sub

ThisWorkbook.Sheets("" & Staff).delete

ThisWorkbook.Sheets("Data").Activate
Range("A1").Select
Do While ActiveCell <> ("" & Staff)
ActiveCell.Offset(1, 0).Activate
Loop
ActiveCell.delete

Application.DisplayAlerts = True
End Sub
[/VBA]
Any help would be much appreciated as to what im doing wrong

Mark

[mdmackillop]

Try
[VBA]
Option Explicit
Sub Removestaffmember()
Dim Staff As String
Dim Answer As Long
Staff = InputBox("Please Enter The Name Of The Staff Member You Want To Remove", " Remove Staff Member")
Answer = MsgBox("Deleting will permanently remove the record, do you want to continue?", vbOKCancel)
Application.DisplayAlerts = False
If Answer <> vbOK Then Exit Sub
ThisWorkbook.Sheets(Staff).Delete
ThisWorkbook.Sheets("Data").Columns(1).Find(Staff).Delete
Application.DisplayAlerts = True
End Sub

[/VBA]

[Bob Phillips]

Try

ActiveCell.ClearContents

instead of

ActiveCell.delete

[MarkNumskull]

Thanks a million MD that was perfect, much appreciated!