Best way to create overview sheet showing elements of monthly sales forecast sheets from staff

[shespoke]

Hi - I need to create an overview sheet which pulls rows from each team member's sales sheets if they are more than 75% qualified. These are in tabs in the same Workbook.

So, if a cell in sheet KIRSTY column J (Qualified) is either 75% or 100%, then that whole row should be copied into another sheet 'TARGET 75'. Same for sheet JACK, sheet AMY etc.

I'll then be able to filter these results to provide a high level overview for the directors, filtering by Sales Manager etc.



I've been googling for ages and tried various IF formulas to get this working, but I've concluded it's beyond my limited understanding

If anyone could help me out it would be hugely appreciated!

 

[ravishankar]

paste the following codes in View > macro > viewmacro>create > paste

Sub shespoke()
Cells(1, 1) = "sheet names"
For a = 1 To Sheets.Count
If Sheets(a).Name <> "Target 75" Then
Cells(a, 1) = Sheets(a).Name
x = Sheets(a).Cells(Rows.Count, 10).End(xlUp).Row
For b = 2 To x
If Sheets(a).Range("J" & b) >= 75 Then
Sheets(a).Range("A" & b & ":z" & b).Copy
Sheets("Target 75").Range("B" & a).PasteSpecial
End If
Next b
End If
Next a
MsgBox "complete"
End Sub

run the macro. it lists sheet names and if any row in J has over 75 it copies the row from A to Z. Hope that helps. if you are unfamiliar wih macros, take a friend's help who knows it.
ravishankar