Macro - Deete Row From Worksheet 1 and Add to Worksheet 2

[djruni]

Hi there,

I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues

One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.

There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.

ive done basic c++ programing years ago, but know nothing about vba or excel macros. pls help ASAP!!!!!! thanks!

Runi

[rylo]

Runi

Try

Please Login or Register  to view this content.

I've assumed that column C contains the status, you are on the sheet Open Issues when the code is run, and check the case of the word Complete.


rylo

[boylejob]

Runi,

Try this and see if it works for you. You will definitely need to do a little testing on this because this is something new I have just learned, but it gets the job done with very little code.

Please Login or Register  to view this content.

On this line of code Field relates to the column where you mark the item as Complete

Please Login or Register  to view this content.

Let me know how this works on your end. If it doesn't work, I have another way of solving this problem. This code worked very well on my end.

[boylejob]

Runi,

This line needs to be changed.

Please Login or Register  to view this content.

To

Please Login or Register  to view this content.

The number is what represents the column not the word Field. Field is one of the arguments and cannot be changed. Criteria1 is another one of the arguments. When you changed Field to E you removed an argument and replaced it with something the compiler could not find. Thus you got "Compile Error: Named argument not found"

Make that change and lets see what happens.

Let me know!

[djruni]

Now I get the following error: Run-time error '1004': That command cannot be used on multiple selections. (but it does filter the records with status '90-completed'. just is not deleted and moving to the end of the list on the closed items worksheet...

I had AutoFiler enabled already for all my column headings (which btw start at row 5 of the worksheet. actual data starts at row 6. row 1-4 is other misc. header info. not sure if it makes a diff.)

If I disable the AutoFilter from the Data menu and re-run the macro, I get this error: AutoFilter method for Range class failed.

[djruni]

Secondary question, is it easy to add a button to my worksheet that i can run this macro with?

[boylejob]

Runi,

Is it possible for you to post a zipped copy of your workbook. I think I'll have a much better chance of solving your problem, if I can look at what you have going on.

[djruni]

I just noticed there are some columns on the open items sheet that do not exist on the closed items list. let me fix that and try the macro. if it still doesnt work, ill put up the file..

Tarun

[boylejob]

Runi,

Alright, just let me know!

[djruni]

Jeff,

I made both worksheets identical in terms of the general setup, columns etc. Column A in sheet 1 corresponds to Coumn A in sheet 2. Now I just get an error "400" when I run the macro.

It filters the items in completd stuatus but is not performing the delete and append to the other sheet. Code is below if you want to take another look. Let me know if u still want the file...

Sub aaa()
Dim wsOpen As Worksheet, wsClosed As Worksheet
Dim rFilter As Range

Set wsOpen = Sheets("Open Items")
Set wsClosed = Sheets("Closed Items")

'Filters for all items marked as "Complete"
wsOpen.Range("A1").AutoFilter Field:=5, Criteria1:="90-Completed"

'Set the range to equal all the items that are "Complete"
Set rFilter = wsOpen.AutoFilter.Range.Offset(1, 0).SpecialCells(xlCellTypeVisible)

'Moves the range to the Closed sheet
rFilter.EntireRow.Copy wsClosed.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

'Deletes the Completed items from the Open sheet
rFilter.EntireRow.Delete

'Takes the filter off
wsOpen.Range("A1").AutoFilter

End Sub

[djruni]

To save time, here's the file. Thanks!

[boylejob]

Runi,

I am not sure why the autofilter method is not working, but I am not going to waste any more of your time with that code.

Like I mentioned several posts back I have another method of doing what you are wanting to do. I have put that code in the attached workbook and it is working on my end. Give it a try and lets see how it works.

I put the code in a regular module instead of the sheet module.

[djruni]

Jeff the code works like a charm! One more small request...

I want to apply the filter for items that are in "99-Cancelled" status also. Those should also be moved to the closed items sheet. whats the additional syntax/code req'd? Below is the latest code you sent.

Sub ccc()

Dim wsOpen As Worksheet, wsClosed As Worksheet
Dim rFilter As Range
Dim lRow As Long

Set wsOpen = Sheets("Open Items")
Set wsClosed = Sheets("Closed Items")

For lRow = 6 To wsOpen.Cells(Rows.Count, 1).End(xlUp).Row Step 1

If wsOpen.Cells(lRow, 5) = "90-Completed" Then
wsOpen.Range("A" & lRow).EntireRow.Copy
wsClosed.Range("A" & wsClosed.Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial
wsOpen.Range("A" & lRow).EntireRow.Delete
lRow = lRow - 1
End If
Next lRow

End Sub

[boylejob]

Runi,

Changed this line

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

That should pick up all of your 90-Completed and 99-Cancelled

I'm sorry the AutoFilter thing did not work. It is a neat concept, but it is something I have just started working with. I have not tinkered with it enough to know how everythnig affects it. I decided it was best to move on to something I knew would work.

Do you mind telling me where you are located? I always find it interesting to see where people are from on this forum