Macro to clear content and put back a formula

  • Thread starter Thread starter miker1999
  • Start date Start date
M

miker1999

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
 
.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
 
Back
Top