Macro to clear content and put back a formula
 

Hello,
I have a macro that takes a row from Sheet1 and copies it to the botto
of Sheet2(sheets name is "Did Not Meet Hiring Criteria"), then remove
the data from a few cells in Sheet1 and puts in a little data and end
up in Sheet2.

My problem is two things.
1) Once it clears the contents in Sheet1, I would like to put a formul
back in cell M (formula is... =W#).

2) I would like for the user to end up in Sheet2 and in Column L of th
row they just pasted. (I have some code in the macro, but it doesn'
always work right...)

Help?! I am trying...just can't quite get it.

Here is the code:

Sub NotMeetCriteria()
Dim rng As Range
Set rng = Sheets("Did Not Meet Hiring Criteria").Range("A"
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy Destination:=rng
With Cells(ActiveCell.Row, 1)
.Offset(0, 8 - 1).Resize(1, 9).ClearContents
.Value = "1-OPEN"
End With
rng.Worksheet.Activate
Cells(ActiveCell.Row, 12).Select

End Su

--
Message posted from http://www.ExcelForum.com

Macro to clear content and put back a formula
 

.Offset(0, 12).Formula = "=W" & .Row
.Value = "1-OPEN"

instead of
rng.Worksheet.Activate
Cells(ActiveCell.Row, 12).Select

try
Sheet2.Activate
Cells(rng.Row, 12).Select


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"miker1999 " wrote in message
...
Hello,
I have a macro that takes a row from Sheet1 and copies it to the bottow
of Sheet2(sheets name is "Did Not Meet Hiring Criteria"), then removes
the data from a few cells in Sheet1 and puts in a little data and ends
up in Sheet2.

My problem is two things.
1) Once it clears the contents in Sheet1, I would like to put a formula
back in cell M (formula is... =W#).

2) I would like for the user to end up in Sheet2 and in Column L of the
row they just pasted. (I have some code in the macro, but it doesn't
always work right...)

Help?! I am trying...just can't quite get it.

Here is the code:

Sub NotMeetCriteria()
Dim rng As Range
Set rng = Sheets("Did Not Meet Hiring Criteria").Range("A" &
Rows.Count).End(xlUp).Offset(1, 0)
ActiveCell.EntireRow.Copy Destination:=rng
With Cells(ActiveCell.Row, 1)
Offset(0, 8 - 1).Resize(1, 9).ClearContents
Value = "1-OPEN"
End With
rng.Worksheet.Activate
Cells(ActiveCell.Row, 12).Select

End Sub


---
Message posted from http://www.ExcelForum.com/