Master Sheet to copy specific rows to corresponding sheets

Question

I have a worksheet on EXCEL 2013 that has a MASTER SHEET with all of the students names and grades. I have created a table with the students names are on Column A and their Period (class) is on Columns B-H. I have sheets with the Case Workers name labeled on the bottom tab and their list of students they track in their sheet.

Here is a sample code someone gave me that didn't work:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns("A:A")) Is Nothing Then Exit Sub
Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Application.EnableEvents = True
Application.ScreenUpdating = True

End Sub

Here is a sample of my worksheet:

MASTER SHEET TAB

Column A row 1: Name 
Column B row 1: Period 1
Column C row 1: Period 2
Column D row 1: Period 3
Column E row 1: Period 4
Column F row 1: Period 5
Column G row 1: Period 6
Column H row 1: 7

Column A row 2: Skywalker, Luke (example)
Column B row 2: F1, GUITAR
Column C row 2: C, HEALTH
Column D row 2: B, GEOMETRY
Column E row 2: A, YR1 ALGEBRA1
Column F row 2: C+, US HISTORY
Column G row 2: B, ENGLISH
Column H row 2: (BLANK)

ELIZABETH (SHEET)

Same as above format.

Copy student from master sheet to corresponding name on sheet.

This may be easier adding information from the separate sheets and consolidate them to the master.

Can you re-format the data to match more how Excel is presented? Doing the columns going down makes it tricky to work in Excel with. Also, how isn't the current code working? Are there errors? Is it running, but not returning what you expect, etc? — BruceWayne
Sorry, I didn't know how to write it on the question box. I have the columns and rows formatted the way EXCEL has it. I added the code, changed the grade on a student from the MASTER and nothing changed from the Case Workers tab. — Aki Roche
I made a sample file of what it looks like. How do I send it? — Aki Roche

Unknown Unknown · Accepted Answer · 2017-10-18T21:49:04

The OP says that the worksheets are named after the students' Caseworkers then and the Caseworkers tabs have a list of the students that they track. So what you actually have to do is search each worksheets for the student's name and then update their class schedule.

Note: This assumes that the student names are listed in column A of the caseworkers' worksheets.

Batch Students

If you wanted to process all the students at one time this will work.

Sub ProcessStudents()
    Application.ScreenUpdating = False

    Const MASTERSHEETNAME As String = "MASTER SHEET"
    Dim cell As Range, ws As Worksheet
    Dim students As Object
    Set students = CreateObject("Scripting.Dictionary")

    With ThisWorkbook.Worksheets(MASTERSHEETNAME)
        For Each cell In .Range("A2", .Range("A" & .Row.Count).End(xlUp))
            If Not students.Exists(cell.Value) Then students.Add cell.Value, cell.Offset(0, 1).Resize(1, 7).Value
        Next
    End With

    For Each ws In ThisWorkbook.Worksheets
        With ws
            If UCase(ws.Name) <> UCase(MASTERSHEETNAME) Then
                For Each cell In .Range("A2", .Range("A" & .Row.Count).End(xlUp))
                    If students.Exists(cell.Value) Then students.Add cell.Offset(0, 1).Resize(1, 7).Value = students(cell.Value)
                Next
            End If
        End With
    Next

    Application.ScreenUpdating = True
End Sub

Update using the Worksheet_Change event

Update as a Student's schedule is being changed.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Not Intersect(Target, Range("H2", Range("A" & Row.Count).End(xlUp))) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

    If Cells(Target.Row, 1) <> "" Then ProcessStudent Cells(Target.Row, 1)

    Application.ScreenUpdating = True
End Sub

Sub ProcessStudent(Target As Range)
    Dim cell As Range
    Dim students As Object
    Set students = CreateObject("System.Collections.ArrayList")

     For Each ws In ThisWorkbook.Worksheets
        With ws
            If ws.Name <> Target.Parent.Name Then
                Set cell = .Find(What:=FindString, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                If Not cell Is Nothing Then
                    cell.Resize(1, 8).Value = Target.Resize(1, 8).Value
                End If
                Exit For
            End If
        End With
    Next
End Sub

Master Sheet to copy specific rows to corresponding sheets

1 Answers

Batch Students

Update using the Worksheet_Change event