Excel Macro - Move & Delete Data

Microsoft Excel 2003 (full)
February 8, 2010 at 08:46:13
Specs: Windows XP
I am new to VBA and am trying to create Macro for Excel 2003. I understand the logic behind it but when I recorded the macro it didn't capture what I wanted.

I want data pulled & deleted from Worksheet A (TASKS) to Worksheet B (COMPLETED) depending on value entered in colum F (worksheets are exactly same).

My rows start with A3 and end I3 all the way A500-I500. If 100% is entered in column F... I want the row associated to be deleted from TASKS and moved to COMPLETED in the same cells so that it's under the same section.

See More: Excel Macro - Move & Delete Data

Report •

February 9, 2010 at 06:31:07

Here is a macro that moves rows from the TASKS worksheet to the COMPLETED worksheet based on the value 100% being entered in a cell in column F of the relevant row.

The Change event is used to detect the change in a cell value on the worksheet and then the changed cell is tested to see if it is both in column F and contains '100%'.

If that is the case, it copies the whole row, to the same row on the Worksheet named COMPLETED and then clears the contents of the row on the TASKS worksheet.

Note that for this to work automatically, the 100% in column F must be entered by hand - the change event will not trigger if the 100% is generated by reference to another cell.

Test this out on a copy of your workbook - see warning note at end of this post.

To add code, right-click on the name Tab for the TASKS worksheet.
Select View code.
In the window that options paste the following:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd
Application.EnableEvents = False
'test if changed cell is in column F and that it contains 1 (100%)
If Target.Column = 6 And Target.Text = "100%" Then
    'copy to same row on 'Completed' Worksheet
    Target.EntireRow.Copy _
        Destination:=Worksheets("COMPLETED").Range("A" & Target.Row)
    'Clear source row data
End If
Application.EnableEvents = True
Exit Sub

'error handler
Application.EnableEvents = True
End Sub

Use ALT+f11 (Alt key and function key 11 pressed together) to return to the main Excel window.

Enter 100% (presumably column F is formatted for Percentages with no decimal points). The appearance of the 100% in the cell must match what is shown in the code.

As there is No Undo for macros, make sure that you backup your workbook regularly.

Test this on a copy of your workbook to ensure that it works as expected. I have only tested it on limited sample data in my environment. Your environment and data will be different.

Note that I cleared the source data, rather than deleting the row.
If you delete the row, the next row takes on the deleted row's number, and when it reaches 100% it will copy to the already used row on the Completed worksheet.


Report •
Related Solutions

Ask Question