Solved: Remove Details From List

[Hoopsah]

Hi

I have attached some code that I have done - but I can only get it to do half the job :banghead:

I can get it to remove the line but I want it to only remove the lines that have data in column E (Leave Date)

I have been going mental trying If statements to no avail. My code is:

Sub PasteRowDelete()
' Macro recorded 30/10/2008 by Gerry McNally

ActiveCell.EntireRow.Select
Selection.Cut
Sheets("Deleted FTE").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("Employee_Data").Select
Selection.Delete Shift:=xlUp
ActiveCell.Select
Application.ScreenUpdating = True
End Sub

[Bob Phillips]

I am not clear what works and what you need Gerry. Can you give an example?

[Hoopsah]

Hi Bob,

I've attached a copy of my worksheet.

If I am in the first tab (Employee Data) and I run the macro PasteRowDelete it simply takes the line that it is currently on and moves the row to Deleted FTE

It's Rubbish!!

At the moment if the user inputs details to the FTE Details screen it will update the employees list.

What I am trying to get is for the user to input details in the FTE Details screen, but if they complete the field Leave Date then instead of updating the employees sheet it will remove the row from the list and copy it into Deleted FTE sheet.

Probably looking for something I can add to the Submit Details macro button that already exists.

[Bob Phillips]

To get this clear in my mind.

Would they be putting all the details in the submit form and it gets added to employee and you want it removed, or it is already on employee so they just specify the employee number and leave date and then it gets moved?

[Bob Phillips]

I will assume the latter



Public Sub SubmitDetails_FTE()
'
' SubmitDetails Macro
' Macro recorded 10/10/2008 by Gerry McNally
'
Dim pos As Long
Dim NextRow As Long
Dim shEmp As Worksheet
Dim shDeleted As Worksheet
Dim shForm As Worksheet


Set shEmp = Worksheets("Employee_Data")
Set shDeleted = Worksheets("Deleted FTE")
Set shForm = Worksheets("FTE Details")

On Error Resume Next
pos = Application.Match(Range("G9").Value, shEmp.Columns(1), 0)
On Error GoTo 0

If (Range("G9").Value = "") Then
MsgBox "Error - FTE Name Required"
Else

With shEmp

If pos > 0 Then

If shForm.Range("G21").Value <> "" Then

If IsDate(shForm.Range("G21").Value) Then

NextRow = shDeleted.Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Rows(pos).Copy shDeleted.Cells(NextRow, "A")
shDeleted.Cells(NextRow, "E").Value = shForm.Range("G21").Value
.Rows(pos).Delete
Else

MsgBox "Leave date must be a valid date"
End If
Else

Application.ScreenUpdating = False
.Cells(pos, "A").Value = shForm.Range("G9").Value
.Cells(pos, "B").Value = shForm.Range("G12").Value
.Cells(pos, "C").Value = shForm.Range("G15").Value
.Cells(pos, "D").Value = shForm.Range("G18").Value
.Cells(pos, "E").Value = shForm.Range("G21").Value
End If
Else
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
.Cells(NextRow, "A").Value = shForm.Range("G9").Value
.Cells(NextRow, "B").Value = shForm.Range("G12").Value
.Cells(NextRow, "C").Value = shForm.Range("G15").Value
.Cells(NextRow, "D").Value = shForm.Range("G18").Value
.Cells(NextRow, "E").Value = shForm.Range("G21").Value
End If
End With

shForm.Range("G9,G12,G15,G18,G21").ClearContents
shForm.Select
Range("G9").Select
MsgBox "Copied"
'... do your stuff
End If

Call Sort_Employee_Data
Sheets("FTE Details").Select
Application.ScreenUpdating = True
Range("G9").Select

End Sub

[Hoopsah]

What Can I say??

That is fantastic Bob, does exactly what I wanted - deleting it from the employee list means that it does not show up on the drop downs in worksheets.

Wish I could say more than Thanks, but Thanks Bob your a star

:thumb

Gerry