extract multiple cells if condition is true then next row

[souch]

Dear all,

I have created a database (data sheet) and I would like to copy to my other sheet "report" all the cells in column C which are greater than my limit in cell C2 and the corresponding time of column B (with no blank cells). Anyone has an idea on how to build the macro for it?

I have already tried all that I knew without macro, using if, vlookup or match but it seems to not be working well... If I understand the posts on the forum, a small macro would be the best but as it's my first steps, I have failed so far. I would need something like IF( cell> limit, copy cell to report, go to next row until ligne 150)

data.xls

[PCI]

It means a filter in sheet data is enough?
With a macro to prepare it.

[souch]

Maybe, as it's my first steps then I don't really know how to start...
Would that make all the data over the limit with the corresponding dates appear in another sheet?

Thanks for such a quick reply

[souch]

Maybe, as it's my first steps I don't really know how to start...
Would such a filter make all the data over the limit with the corresponding dates appear in another sheet?

Thanks for such a quick reply

[PCI]

See how next code can help

Sub Prepare_Data()
Dim Limit As Double
Dim LastRow As Long
Dim WkRg  As Range
    Limit = Range("Limit")
    Set WkRg = Range("$B$6:$E" & Range("C" & Rows.Count).End(xlUp).Row)
    If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False '  REMOVE  AUTOFILTER  IF  EXIST
    WkRg.AutoFilter Field:=2, Criteria1:=">" & Limit
End Sub
Sub CopyData()

    Sheets("report").Range("B5:C" & Range("C" & Rows.Count).End(xlUp).Row + 1).ClearContents
    Limit = Range("Limit")
    Set WkRg = Range("$B$6:$C" & Range("C" & Rows.Count).End(xlUp).Row)
    If (ActiveSheet.AutoFilterMode) Then ActiveSheet.AutoFilterMode = False '  REMOVE  AUTOFILTER  IF  EXIST
    WkRg.AutoFilter Field:=2, Criteria1:=">" & Limit
    WkRg.Offset(1, 0).Copy Destination:=Sheets("report").Range("B5")
End Sub

[souch]

Thanks PCI! I think we are getting closer, but now that I see how it works, the filter will not be the best. Indeed, I will probably extend my file with other data (I should have put it from the beginning but I wanted to start simple). Then, a filter will hide my other data.

Attachement : data2.xls

The macro should select the data (and corresponding time) over the limit (different one for each set of data, data1 is cell C2, data2 is F2 etc.),
Copy these cells into the report sheet

Is it possible?

Many thanks

[PCI]

Here we are

Option Explicit
Option Base 1
Sub CopyData()
Dim TimeTbl()
Dim DataTbl()
Dim ICol As Long
Dim Limit As Double
Dim F As Range
Dim WkRg As Range
Dim LastRow As Long
Dim Result()
Dim I  As Long, II  As Long
    ICol = 2
    With Sheets("report")
        .Range("B5", .Range("B5").SpecialCells(xlCellTypeLastCell)).ClearContents
    End With
    With Sheets("Data")
        LastRow = .Range("B" & Rows.Count).End(xlUp).Row
        Set WkRg = Range(.Cells(7, "B"), .Cells(LastRow, "B"))
        TimeTbl() = WkRg
        For Each F In Range(.Range("C2"), .Cells(2, Columns.Count).End(xlToLeft))
            If (F.Value <> "") Then
                Limit = F.Value
                Set WkRg = Range(.Cells(7, F.Column), .Cells(LastRow, F.Column))
                DataTbl() = WkRg
                ReDim Result(1 To LastRow, 1 To 2)
                II = 1
                For I = 1 To UBound(TimeTbl, 1)
                    If ((DataTbl(I, 1) <> "") And (DataTbl(I, 1) >= Limit)) Then
                        Result(II, 1) = TimeTbl(I, 1): Result(II, 2) = DataTbl(I, 1): II = II + 1
                    End If
                Next I
                Sheets("report").Cells(5, ICol).Resize(UBound(TimeTbl, 1), 2) = Result
                ICol = ICol + 3
            End If
        Next F
    End With
End Sub

[souch]

Thank you very much PCI! This filter seems to be working really well.

The only thing is that for me as a very beginner, it's difficult to fully understand the code and modify it in the future if needed.

Thanks a lot!

[PCI]

souch you're welcome.
Do worry about the maintenance the code is very simple, there is a lot of people here or around to understand it and help you if needed
PCI