Copy and paste Rows based on 2 critera's

Hi Guy,

i am after a VBA code which based on 2 lots of selected criteria (from a data validation list/drop down list) will return the relevant rows.

I have a dashboard sheet which im looking to have 2 drop down selections

Week No
Percentage

Depending on what criteria the user selects the relevant rows from that sheet (Named Week1, week2, e.t.c) is returned that match the percentage required looking at.

for example.

If the user selects Week2 from the data validation list and 15% from the second data validation list, i am after a VBA code which looks at Week2 sheet and returns all rows with a percentage of 15% or less to the dashboard.

i tried recording a macro but i dont know what the function is to search for specific sheet based on selection the macro just always looks at the recorded sheet and the Percentage i filtered.

Thanks

hi thanks for the reply see attached work book.
Ive highlighted in red on the supposed dashboard sheet the 2 criteria selections plus the columns i would love to be able to get automatically copied across based on the criteria,
i did record a macro but it dosent have the facility to search for the relevent data it just copies across the same data i record every time.

That is amazing, thank you so much I would have never of go this far and worked that out,
Just 2 things ive missed column D so could you direct me how to just add that in the info copied across. i asume its somewhere here

sr = .Range("C3", .Range("C" & Rows.Count).End(xlUp)).SpecialCells(12).Row
Set rng = Union(.Range("C" & sr & ":C" & lr).SpecialCells(12), _
.Range("G" & sr & ":N" & lr).SpecialCells(12))

also if i was to copy across some new data to my dashboard could i just use the following before the code above to clear any unwanted rows before hand?


Range("A1:C10").ClearContents
(Just edit my range of cells to clear)


Thank so much

Ahh Brilliant new it was somewhere around there learning slowly =)
Now onto the next great excel idea..
thank you for your help