excel-auto copy a row to another sheet

Microsoft Excel 2003 (full product)
August 13, 2010 at 23:21:49
Specs: Windows Vista

i have a worksheet with lots of tasks records, some completed, in progress, overdue, etc. can vb auto copy the entire row over to a corresponding status sheet if the status column answer to criteria?
sheet1 -Main
column a =tasks
column b =status
status drop-down list is in-progress, completed, pending, overdue

sheet2 -in_progress
sheet3 -completed
sheet4 -pending
sheet5 -overdue

anyone can help?

See More: excel-auto copy a row to another sheet

Report •

August 14, 2010 at 14:36:20
You should be able to do it using VB
To get Help with VB
Open Exel
Click the Microsoft Office Button , and then click Program Name Options, where Program Name is the name of the program you are working in, for example, Word Options.
Click Popular, and then select the Show Developer tab in the Ribbon check box.
If you need more help I suggest you repost in the Office Software section.

Report •

August 18, 2010 at 06:25:54

Your request is quite complicated!

When you add a new task on the Source worksheet, you may enter the status before all the other data has been completed.
As a result copying the row to the appropriate status worksheet when the status is entered, will only copy partial information.

To overcome this, this macro undertakes a copy to the appropriate status sheet whenever anything about the task is changed.
As the copy to the appropriate status sheet uses the next empty row to copy to, it then has to go back up that sheet to find and delete any previous copies of that task.

A second major issue is that when the status of a task is changed, it will be copied to its new status sheet, but there is still a copy of the task in another sheet.
Although there is a logical progression, it is not absolute, so pending could move to in_progress or to overdue.
As a result this macro goes through all the other three status sheets and deletes any copy of the task.

The following macro works by using Excel's Change event.
Any change on a worksheet triggers this change event and a macro can be triggered by that change event.

The macro then tests to see if the change was in an appropriate cell and if it was, it runs the rest of the code to copy and delete tasks.

This macro assumes that the task has a task name or some form of ID in column A, a status in column B and data about the task in column C to G.
As you didn't say which columns contained data about the task, you will need to modify the code in this line to match your data:

    'copy columns A to G (=7 cols.) - adjust as appropriate
    rngSrc.Offset(0, -1).Resize(1, 7).Copy _

Note that all the text after the comment line is one line of code. The space and underscore character " _" are a line-continuation in Visual Basic

You will also have to change the macro's test for which cells to respond to, so change <8 to match:

'test if the changed cell is in columns B to G
'must action changes in associated data, not just status col. B
If Target.Column > 1 And Target.Column < 8 Then

As this macro responds to the Source worksheet's change event, the code is 'attached' to the worksheet containing the source data.
Right-click on the worksheet's name tab and select 'View code'
Paste the following into the large Visual Basic code window:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngSrc As Range
Dim rngDest As Range
Dim wsDest As Worksheet
Dim n As Double

On Error GoTo ErrHnd

'disable events - so that changes made by this code
'do not re-trigger it
Application.EnableEvents = False

'test if the changed cell is in columns B to G
'must action changes in associated data, not just status col. B
If Target.Column > 1 And Target.Column < 8 Then
    'set rngSrc to Column B (status) on changed row
    Set rngSrc = ActiveSheet.Range("B" & CStr(Target.Row))
    'actions based on word entered in column B
    Select Case rngSrc.Text
        Case "in_progress"
            'find next empty row on the in-progress sheet
            Set rngDest = Worksheets("in_progress") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
            'Set rngDest = rngDest.Offset(1, 0).Offset(1, 0)
        Case "completed"
            'find next empty row on the completed sheet
            Set rngDest = Worksheets("completed") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "pending"
            'find next empty row on the pending sheet
            Set rngDest = Worksheets("pending") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case "overdue"
            'find next empty row on the overdue sheet
            Set rngDest = Worksheets("overdue") _
                    .Range("A" & CStr(Application.Rows.Count)) _
                    .End(xlUp).Offset(1, 0)
        Case Else
        'no match - so error out
        GoTo ErrHnd
    End Select
    'move the data to the required worksheet
    'copy columns A to G - adjust as appropriate
    rngSrc.Offset(0, -1).Resize(1, 8).Copy _
    'now delete it from any other destination worksheet
    'loop through all worksheets
    'test if it is one of the four named destination sheets
    'but not the one we just copied it to
    For Each wsDest In ActiveWorkbook.Worksheets()
        If (wsDest.Name = "in_progress" _
                    Or wsDest.Name = "completed" _
                    Or wsDest.Name = "pending" _
                    Or wsDest.Name = "overdue") _
                    And wsDest.Name <> rngDest.Worksheet.Name Then
            'find matching ID - work from end of used range
            'just in case there is a duplicate entry
            For n = wsDest.UsedRange.Rows.Count To 2 Step -1
                'find matching Task
                If wsDest.Range("A" & CStr(n)).Text = _
                                    rngSrc.Offset(0, -1).Text Then
                    'match found, so delete row
                    wsDest.Range("A" & CStr(n)).EntireRow.Delete
                End If
            Next n
        End If
    Next wsDest
    'now test the worksheet we just copied to - for duplicates
    'start at row above the one we copied to
    With rngDest.Worksheet
        For n = rngDest.Row - 1 To 2 Step -1
            If .Range("A" & CStr(n)).Text = _
                        rngSrc.Offset(0, -1).Text Then
                'delete row
               .Range("A" & CStr(n)).EntireRow.Delete
            End If
        Next n
    End With
End If

're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

From the Visual Basic menu bar select File - Save
Use Alt+f11 (The Alt key and function key #11 pressed together) to return to the main Excel window.

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on very limited sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

Note that when this macro runs, it deletes data - so maintaining backups is very important.


Report •

August 19, 2010 at 20:43:05
Thanks guys.

Humar -thanks. i am trying it out...

Report •

Related Solutions

February 27, 2011 at 01:45:11
I have a similar scenario... just a bit more complicated given 3 conditions... can you help me?

structure of my main worksheet named MAIN is as follows:
column A - date (header is AA)
columns B-F - text (headers are BB - FF)
column G - number (header is GG)
columns H-I - date (headers are HH - II)
columns J-M - number (headers are JJ - MM)
columns N-P - date (headers are NN - PP)
columns Q-R - text (headers are QQ - RR)

destination sheets are named WW, SN, TW, CB, YL, TK

what I'd like to have is that
1. IF the text in indicated in column Q is WW, it goes to sheet WW, if SN, it goes to sheet SN and so forth an so on BUT...
2. those that will go to those sheets are only those whose date in colume P is the current date... AND
3. only data in columns B, C, J, K & M must go to the destination sheets


Report •

Ask Question