Extract Start & End Date in list, linked to time

**BartDeHertogh** · 01-11-2010, 06:28 AM

Hi,
I've a sheet with Actions, dates and time (Data).
The time is only noted next to the end date of the action.
I'd like to extract only the action with a time result from this listing to another sheet (Result) . The fomat should be: Action;Start date; End date; Time.
If other actions don't have a time logged next to the end date... this should not be ewtracted to the other sheet.
I've put an example sheet in annex.
Many thanks for your help

**royUK** · 01-11-2010, 07:21 AM

Your columns don't correspond on each sheet,which would make this easier. Also it would be best to use AutoFilter so the empty row below the headers needs removing.

The example works but the result sheet could do with reformatting to match the other sheet,otherwise the code will need amending

**royUK** · 01-11-2010, 07:27 AM

This copies & maintains the format

Option Explicit

Sub Macro1()
    Dim rData As Range
    Dim NextRw As Long
    With Sheet2
        NextRw = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
    End With
    Set rData = Sheet1.Range("E6").CurrentRegion
    rData.AutoFilter Field:=4, Criteria1:="<>"
    rData.Offset(1, 0).Resize(rData.Rows.Count - 1, rData.Columns.Count - _
                                                    1).Copy Sheet2.Cells(NextRw, 2)
    rData.Offset(1, 0).Resize(rData.Rows.Count - 1, _
                              rData.Columns.Count).Columns(4).Copy Sheet2.Cells(NextRw, 7)
    rData.AutoFilter
End Sub

**BartDeHertogh** · 01-11-2010, 08:47 AM

Hi RoyUK,

Thanks for the code !
I think I need to give more info:
- Data Sheet:
* data in cells can be added or deleted (more actions can be added or deleted)
* end & start date are found in "Date" column (expl.: Action 1 starts on 01/02/2010 and ends on 09/02/2010). "Revised date" column is the same idea.

- Result:
* Format has to be the way it's shown... (sorry

)
* The result is (auto)updated completely when the datasheet is updated.

Thx,
B.

**royUK** · 01-11-2010, 12:24 PM

Have you tried the amended code?

**BartDeHertogh** · 01-12-2010, 03:47 AM

Goodmorning RoyUK,

Yes, I've tried the amended code and that works fine !
The data is filtered and the time is put in the wright cell

Only the the start&end date and revised start&end date are wrong.
When you execute the macro line are added again under the previous macro execution.
Could you make it so that it functions like described in my previous post ?
Many thanks for the code. Much appreciated

B.