VBA Code required

[Damo10]

Hi,

I have a workbook with a "Data" sheet that contains 3 columns, in col A are names and in col B are dates and im col C various data.

What I would like is on a report sheet 2 options to retrive certain data.

option1 to enter a name in cell A2 and then all the data in the rows that contain that name be listed from cell B5 down.

Option 2 is to enter a start date in cell A6 and a finish date in cell A9 and to have all the data in the rows that are equal to or between these dates listed from cell B5 downwards

Hope that somebody can help

Regards

 

[VoG]

Have you looked ar AutoFilter?

 

[Damo10]

Hi,

Yes I have looked at that but the Data sheet is a hidden sheet and which I dont want the other users of the report to be able to access.

 

[Marcelo Branco]

Hi Damo10

Assuming the data begin in row 2 in Sheet Data, maybe this
(try it on a test-workbook)

Sub listByName()
    Dim wkData As Worksheet, wkReport As Worksheet
    Dim lastRow As Long, i As Long, lin As Long
 
    Set wkData = Sheets("Data")
    Set wkReport = Sheets("Report")
 
    wkReport.Range("B5:D200").Clear
 
    With wkData
        lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
 
        lin = 4
        For i = 2 To lastRow
            With .Range("A" & i)
                If UCase(.Value) = UCase(wkReport.Range("A2").Value) Then
                    lin = lin + 1
                    wkReport.Range("B" & lin) = Format(.Offset(0, 1), "mm/dd/yyyy")
                    wkReport.Range("B" & lin).Offset(0, 1) = .Offset(0, 2)
                End If
            End With
        Next i
    End With
End Sub

For the second one you have to adjust the IF to something like that

With .Range("B" & i)
                If .Value >= wkReport.Range("A6").Value _
                    And .Value <= wkReport.Range("A9").Value Then

and also adjust the Offset(s), considering you are in column B.

HTH

M.

 

[Damo10]

Thanks that works great

 

[Marcelo Branco]

You are welcome.

Tks for the feedback

M.