Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to clear up useless content within worksheet? | Excel Discussion (Misc queries) | |||
Clear Cell Content Query | Excel Discussion (Misc queries) | |||
How to clear content from unprotected Field | Excel Worksheet Functions | |||
Clear Cells Content | Excel Discussion (Misc queries) | |||
Clear the content of a worksheet using VBA | Excel Programming |