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

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

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

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

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


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


"miker1999 " wrote in message
...
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 the
contents of the row that the user selected in "1-OPEN" only of columns
H:P.

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

3.. I would like the user to end up in the sheet ("Did Not Meet Hiring
Criteria") in the row that was just pasted in and in column L of that
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!
Mike


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

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

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)
www.rondebruin.nl



"Rob van Gelder" wrote in message ...
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


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


"miker1999 " wrote in message
...
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 the
contents of the row that the user selected in "1-OPEN" only of columns
H:P.

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

3.. I would like the user to end up in the sheet ("Did Not Meet Hiring
Criteria") in the row that was just pasted in and in column L of that
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!
Mike


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

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

his worked perfect.. thanks!
Mik

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