Results 1 to 5 of 5

Thread: Transfer Information From One Sheet To Another Based On Criteria

  1. #1
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12

    Transfer Information From One Sheet To Another Based On Criteria

    Greetings,

    Please see attached spreadsheet.

    On the task sheet I require any Task Descriptions tasks which are marked Completed transferred over to the Archive sheet by utilising the archive button.Once the Completed Task/s has been transferred it is deleted from the Task sheet (row deleted so as no empty rows between tasks)

    Best Regards

    Jeff
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Try this

    Code:
    Sub ArchiveCompleted()
    
        Dim rng As Range
        
        With Worksheets("TASKS")
            For Each rng In .Range("E5:E" & Application.Max(.Cells(.Rows.Count, "E").End(xlUp).Row, 5))
                If LCase(rng.Value) = "completed" Then
                    With Worksheets("ARCHIVE")
                        .Cells(.Rows.Count, "B").End(xlUp)(2).Resize(, 2).Value = Array(rng.Offset(, -3).Value, rng(1, 2).Value)
                        rng.Offset(, -3).Resize(, 5).ClearContents
                    End With
                End If
            Next rng
            With .Range("$B$4:$F$" & Application.Max(.Cells(.Rows.Count, "E").End(xlUp).Row, 5))
                .RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), Header:=xlYes
                With .Borders
                    .LineStyle = xlContinuous
                    .Weight = xlThin
                End With
            End With
        End With
        
    End Sub
    Note: This will only work in Excel 2007 or above. If you are using a lower version, post back.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    Can the code be linked to a ActiveX control
    Jeff

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Yes it can. But the control you have on your sheet is not an activex control. You can just right click on that control, and assign macro by selecting the macro name.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Senior Member
    Join Date
    Jul 2013
    Posts
    102
    Rep Power
    12
    Hi

    This code is very good but can it remove the empty rows from the task sheet once the data has been transferred to the Archive sheet.This way there is no gaps between tasks in the Task sheet

    Best Regards

    Jeff
    Attached Files Attached Files

Similar Threads

  1. Replies: 2
    Last Post: 04-10-2013, 12:40 AM
  2. Summing Data based on certain criteria
    By Howardc in forum Excel Help
    Replies: 7
    Last Post: 01-30-2013, 07:12 PM
  3. Split data based on criteria
    By Mahesh.sreekakulam in forum Excel Help
    Replies: 3
    Last Post: 06-08-2012, 09:30 PM
  4. Lookup Based on Criteria
    By maruthi in forum Excel Help
    Replies: 2
    Last Post: 02-15-2012, 08:49 AM
  5. Find Value Based On Criteria
    By excel_learner in forum Excel Help
    Replies: 10
    Last Post: 07-30-2011, 10:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •