Copy data from one sheet to another based on criteria

[SReale33]

On sheet one I have event dates in column C starting on row 7 and ending on row 105, event names in column D starting on row 7 and ending on row 105, and event type drop down in column F starting in row 7 and ending on row 105 with a color coded fill that changes with event type in column E starting in row 7 and ending on row 105. I need to move events that have the event type listed as a TASKS & ERRANDS from sheet 1 to a task checklist on sheet 2. I only need the event name (column D rows 7 to 105) copied over to sheet 2. I do not need the date, the event type or color code (because its only tasks and errands anyway) to be copied to sheet 2. The sheet names are: Sheet 1=Event List, sheet 2=Task checklist. On sheet 2 - task checklist, I need the event name copied to column C starting on row 6 and moving each new entry to the row below (eg. entry 1 row 6, entry 2 row 7, and so on to row 52). I already have manually entered items in this task checklist on sheet 2 and I would prefer not to have to re-enter them. So if the code could accommodate that, that would be great! Please help!!!



Visual explanation:



Event List (Sheet 1) -



A B C D E F



5 Headers -> Date Event Name Event Type (Drop down) <- Headers



6 Input Cell Input Cell Input Cell Add Button (adds inputs & sorts by date in range C7:F105)



7 Sorted list starts . . . . . . . . . .
... . . . . . . . . . . . . . . . .

105 . . . . . . . . . . . . Sorted list ends






Task Checklist (Sheet 2)-



A B C


5 Check Mark Task <- Headers


6 Check Box List Start ->


... Check Box . . . . -> I need sheet one tasks copied in this list area (tasks only)


52 Check Box List End ->


Let me know if you need more info to help me out! I appreciate any help I can get!

 

[JLGWhiz]

See if this works for you

Sub t()
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets(1)
Set sh2 = Sheets(2)
    With sh1
        Intersect(.UsedRange.Offset(6), .UsedRange).AutoFilter 6, "TASKS & ERRANDS"
        .Range("D7", .Cells(Rows.Count, 4).End(xlUp)).SpecialCells(xlCellTypeVisible).Copy _
        sh2.Cells(Rows.Count, 3).End(xlUp)(2)
    End With
End Sub