Pulling Data from one sheet based on value of column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is what I have:
Worksheet that basically has Columns A:Q with headers Last Name, First Name,
Badge Number, Supervisor, etc. and then the rest contain course names such as
Hazcom, Hazmat, etc.

The way that I have the sheet set up is if a user "John Doe" with
Supervervisor "XYZ" has taken a course they get a 1 if they haven't they get
a 0. What I need to do is on seperate sheets within the workbook based on the
course and if they contain a 0 generate a list including (Last Name, First
Name and Supervisor "columns A, B, and F" for instance). So if there are 12
different courses it would generate 12 different sheets such as Hazcom,
Hazmat, etc.

Any help would be greatly appreciated.
 
Assuming row 1 contains the same course names as the sheets. You have to add
the sheet for each course manually. I randomly picked columns 5 To 10 to
contain the 0's. Change this statement as necessary.


Sub missingcourses()

With Sheets("Summary")

Lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
'setup columns to check for 0's
For ColumnCount = 5 To 10
CourseRowCount = 1
For RowCount = 2 To Lastrow

If .Cells(RowCount, ColumnCount) = 0 Then
first = .Cells(RowCount, "A")
last = .Cells(RowCount, "B")
supervisor = .Cells(RowCount, "F")
'assume column headers in row 1 match
'sheet names
With Sheets(.Cells(1, ColumnCount))
.Cells(CourseRowCount, "A") = first
.Cells(CourseRowCount, "B") = last
.Cells(CourseRowCount, "C") = supervisor
CourseRowCount = CourseRowCount + 1
End With
End If
Next RowCount
Next ColumnCount
End With

End Sub
 
Back
Top