Automatically checkling ranges and selecting contents based on a value

**AmyTaylor** · 03-03-2008, 09:43 AM

hi all, can anyone help please?
i attach a zipped excel file which contains the following sheets:

parameters - allows the user to select a given "area" to report on
report - this is where the output would be presented
area a to area d - sheets containing the invidual data collected by each area.

what i am looking for, if possible, is a way, via vba code, to look at the data in the 4 area sheets and, when the Reviewer has been selected in the drop down on the "parameters" sheet cell D3. copy the relevant data to the report sheet.
In my example, you can see that it has returned 6 rows from area A and 1 row from area C.

Any help, gratefully received.
Thanks

**MickG** · 03-03-2008, 12:42 PM

Mr Excell _Amy Taylor_Automatically checkling ranges and selecting contents based on a value
----------------------
Hi, Amy
Try this, I've assumed the linked cell is "parameters"- range("D3").
This seems to work so far!

Dim wksht As Worksheet, oRay, vNm As String
Dim cl As Range, c As Integer, last As Integer
Dim rng As Range, sNm As String
vNm = Sheets("parameters").Range("D3").Value

For Each wksht In ActiveWorkbook.Worksheets

    If wksht.Name <> "parameters" And wksht.Name <> "report" Then
  sNm = wksht.Name

Set rng = Sheets(sNm).Range(Sheets(sNm).Range _
("c1"), Sheets(sNm).Range("c" & Rows.Count).End(xlUp))

    ReDim oRay(1 To rng.Count, 1 To 5)

  For Each cl In rng
    If cl.Value = vNm Then
        c = c + 1
        oRay(c, 1) = wksht.Name
            oRay(c, 2) = cl.Offset(, -2)
                oRay(c, 3) = cl.Offset(, -1)
                    oRay(c, 4) = cl.Value
                        oRay(c, 5) = cl.Offset(, 1)
                     End If
                 Next cl

    End If


last = Sheets("report").Range("a" & Rows.Count).End(xlUp).Row + 1

If c > 0 Then
    Sheets("report").Range("a" & last).Resize(c, 4).Value = oRay
End If
c = 0
Next wksht
MsgBox "Code Complete"

Regards Mick