Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to sort (by one field), copy (based on one field) to another tab,then clear data on original sheet.
I have a spreadsheet with 2 tabs. Pension Log and Pension Log
closed. On the Pension Log, I can sort by a number of fields. This is the macro I have set up to run for the sort: Sub Sort_Status() ' ' Sort_Name Macro ' ActiveSheet.Unprotect Range("A1:L3654").Select Selection.Sort Key1:=Range("j2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Range("A2").Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowSorting:=True End Sub Pension Closed Log is the same as the Pension log but just closed items only. Cell J2 is the status field. It is either an O, I, H or C (C for closed). I can sort this log so that all the closed lines are together. What I want to do is via a manual process (macro button) sort and copy all closed lines over to the Pension closed log and then clear contents on the closed rows (but leave conditional formulas)on the pension log (originator). Pension log is from Cell A2 to L3654. The pension closed log would just keep adding (next blank row down) any closed items from the Pension log for about a year. This might be run once a month or so. By setting this up, I want to be able to keep only active rows on the Pension Log and be able to automate the sort and copy/move of the closed rows over to the Pension closed log. Presently users are using cut and paste causing format and conditional formatting problems. I have the sort function down, but have not been able to write the script to select only rows that equal 'C' in field J2, then copy to the next blank row on the tab 'Pension log closed' and the last is clear contents of any row on the original Pension Log that indicates a 'C' in field J2. thx Mel |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Sheet to new Sheet and clear cells on original sheets | Excel Discussion (Misc queries) | |||
Sort pivot table field (droplist) by month or original data | Excel Discussion (Misc queries) | |||
calculated field based on data field | Excel Discussion (Misc queries) | |||
Replacing data in a row of sheet1 with data in a row of sheet 2 based on a field in both sheets being the same? | Excel Programming | |||
How do I clear the Field pulldowns after the data is cleared? | Excel Discussion (Misc queries) |