Macro help - copy row, clear, and paste on different sheet

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

miker1999

Hi, I need a little more help with a macro.

1... In the code, after the copy of the row, I would like to clear th
contents of the row that the user selected in "1-OPEN" only of column
H:P.

2... I would like column A of the row in "1-OPEN" that the use
selected to change to "OPEN".

3.. I would like the user to end up in the sheet ("Did Not Meet Hirin
Criteria") in the row that was just pasted in and in column L of tha
row. (they will need to type in some text there..)

Here is the code as it stands.

Sub NotMeetCriteria()
ActiveCell.EntireRow.Copy Destination:= _
Sheets("Did Not Meet Hiring Criteria").Range("A"
Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Help...thanks!
Mik
 
Mike,

Assuming that "1-OPEN" is a worksheet:

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 = "OPEN"
End With

rng.Worksheet.Activate
rng.Select
End Sub
 
If the Offset confuse you that Rob use
You can use this also

Cells(ActiveCell.Row, 1).Range("H1:P1").Clear

Or

Range(Cells(ActiveCell.Row, "H"), Cells(ActiveCell.Row, "P")).Clear
Cells(ActiveCell.Row, "A").Value = "Open"


--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)




Rob van Gelder said:
Mike,

Assuming that "1-OPEN" is a worksheet:

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 = "OPEN"
End With

rng.Worksheet.Activate
rng.Select
End Sub
Click to expand...
 
Back
Top