Pull only Specific Data

[jjmalloy82]

I have a code (below) that is doing the original intent. However, I am wondering if there is a way to only pull the first 20 lines of data.

What I would think would be awesome is if:

Let's say on the Dashboard I select the Country "United States" and I also select "Business Unit". I would like for the code to only pull the first 20 lines of data that meet those two conditions.

As you can see below I have my data set up to sort and then pull the data over, what I would like is to be able to sort just as below but add conditions....if I have 40 rows of data and 25 of those meet my two conditions I want the first 20 results to appear on my Dashboard.

Sub TechReview()


Application.ScreenUpdating = False


Dim ws As Worksheet
Dim ws2 As Worksheet


Set ws = Sheets("Dashboard")
Set ws2 = Sheets("Status")


    Range("E15:E35").Select
    Selection.NumberFormat = "0.00"


ws.Range("D11").Value = "Assessor"
ws.Range("E11").Value = "Tech Reviewer"
ws.Range("F11").Value = "Due Date"
ws.Range("G11").Value = "Status"


Sheet8.Visible = xlSheetVisible


Sheet8.Select
Sheet8.Range("A4:cc1000").Select
Selection.Sort Key1:=Range("AR2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal


ws.Select


Sheet8.Visible = xlSheetVeryHidden


'----------------------------------
'COPIES 20 LINES OF DATA
ws.Range("B13:C13").Resize(20).Value = ws2.Range("A4:B4").Resize(20).Value
ws.Range("D13").Resize(20).Value = ws2.Range("AD4").Resize(20).Value
ws.Range("E13").Resize(20).Value = ws2.Range("AI4").Resize(20).Value
ws.Range("F13").Resize(20).Value = ws2.Range("AR4").Resize(20).Value
ws.Range("G13").Resize(20).Value = ws2.Range("T4").Resize(20).Value


ws.Range("A1").Select


End Sub

 

[mperrah]

have you looked at sumproduct as way to accomplish this?

if your source data (sheet8? or Status?) has a set format, you can use dynamic naming of the columns,
then use sumproduct to display matches of what ever criteria you want.

I had a project before I had built a source data sheet and set up a chart summary that I could display data based on start and stop dates in drop down validation cells that I named. cStart and cEnd.

It looks like the data you are interested in is:
ws.Range("D11").Value = "Assessor"
ws.Range("E11").Value = "Tech Reviewer"
ws.Range("F11").Value = "Due Date"
ws.Range("G11").Value = "Status"

so name these columns:
Assessor = OFFSET(Sheet8!$D$11,0,0,COUNTA(Sheet8!$D:$D))
TechReviewer = OFFSET(Sheet8!$E$11,0,0,COUNTA(Sheet8!$E:$E))
DueDate = OFFSET(Sheet8!$F$11,0,0,COUNTA(Sheet8!$F:$F))
Status = OFFSET(Sheet8!$G$11,0,0,COUNTA(Sheet8!$G:$G))

name a cell cStart and put a start date in it
name a cell sEnd and put an end date in it

then in the status sheet you can use something like this:
=IF(A1="","",SUMPRODUCT(--(Assessors=$A1),--(DueDate>=cStart),--(DueDate<=sEnd)))
the if is to show nothing instead of an error if A1 is empty

in a1 put the Assessor name you want to show results
and you'll see the total between the start and stop dates.

If you post your data or upload the workbook I can help line things up, if this sounds like what you're looking to do?