Moving rows

[mornecom]

Hi all,

I am using the "Move rows" macro from #973. We constantly have people leaving the company (some for good and some on assignments) and many new people arrive monthly. I use the macro in two ways: 1. When someone goes on assignment I move their details with the press of a macro button to the "Assignments" worksheet. 2. When we get info of new people's details, it gets entered on the "Arrivals" worksheet. Only when a person physically starts is he/she moved into the "Master" sheet, also by means of the macro .

My question is, can the macro recognise the same function but when I change the "Dept" cell of the active row to "Assignment", that it moves the row to the "Assignments" sheet (without the need for a button)? It will prevent me having to install the newly created toolbar for every user.

Morne

 

[SamTYler]

If Seven of Nine had written the macro. I answer with a resounding "Yes." And if the inventor of #9 wrote it, 'ld also say sure. Boy, his girlfriend was a hottie. I think she is better looking than Seven of Nine. I don't think #9 would even recognise a macro if he grew to be a foot tall.

I have no experience with #973, so I don't have an opinion of his coding skills.

 

[mornecom]

OK, so I wasn't very clear... my apologies. It was the first thing I did this morning before I had a coffee and after this issue was mulling in my head all night long.

The reference was to Episode 973 of the MrExcel podcasts. Instead of sending you there, I decided to copy the macro instead:

Sub AssignedOut()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = ActiveSheet
Set WS2 = Worksheets("Assigned Out")

FirstRow = Selection.Rows(1).Row
RowCount = Selection.Rows.Count

NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1

WS1.Cells(FirstRow, 1).Resize(RowCount, 40).Copy Destination:=WS2.Cells(NextRow, 1)

WS1.Cells(FirstRow, 1).Resize(RowCount, 40).Delete Shift:=xlUp

End Sub

I hope this make more sense now. I would like to know if the macro can respond when you change data in a certain cell to a specific value i.e. have a data validation which includes "Assigned out". The macro should then just respond on that value.

Thank you!

Morne

 

[SamTYler]

That won't be hard, but first I gotta know...Is the "Certain Cell" that contains the string "Assigned Out" only one specific cell on WS1? If so which Cell?

Or is it a cell in a specific column of the table. IOW, in the list of employees(?), is there a column that contains the "assigned Out?" If so, which column.

The Procedure I am imagining will run every time that one cell or any cell in that column is changed to "Assigned Out."

SamT