Duplicating changing columns to another sheet

[jw3times]

So I have a workbook which contains multiple sheets of tables similar to the ones below. In all the sheets, the first two columns (Name and Class) are always the same. The rest of the columns are always different. Whenever someone new comes along, or someone leaves, then I go in and add/delete their name from each sheet. I have eleven different sheets, so it becomes time consuming.

Is there anyway I can just copy all the values from the first sheet into the rest of them?

Naturally, I tried putting "=sheet1!A2" into the second sheet and dragging down. That works, until I insert/delete a row, which I'll be doing a lot of. Basically, if I insert a row between Jane and Juliet and designate them a Senior, I want those two columns to auto populate into all the other sheets, and have the other columns stay blank until I input something there.

I feel like this should be a simple solution, but I just can't figure anything out . Any help would be appreciated!


English Score
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Fall '17[/TD]
[TD]Spring '18[/TD]
[TD]Fall '18[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Freshman[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Sophomore[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Junior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Junior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Juliet[/TD]
[TD]Senior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]



Math Score
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Class[/TD]
[TD]Fall '17[/TD]
[TD]Spring '18[/TD]
[TD]Fall '18[/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]Freshman[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Sophomore[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]Junior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD]Junior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]Juliet[/TD]
[TD]Senior[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Mis-posted.

 

[mumps]

This macro assumes that the sheet where you will be doing all your changes is named "English". Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your "English" sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Try adding/deleting data.

Dim lRowOld As Long
Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
    Dim actRow As Long, lRowNew As Long, ws As Worksheet, srcWS As Worksheet
    Set srcWS = Sheets("English")
    actRow = Target.Row
    lRowNew = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    If lRowOld > lRowNew Then
        For Each ws In Sheets
            If ws.Name <> "English" Then
                ws.Rows(actRow).EntireRow.Delete
            End If
        Next ws
    lRowNew = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    ElseIf lRowOld < lRowNew Then
        If Target.Row = lRowNew Then
            For Each ws In Sheets
                If ws.Name <> "English" Then
                    ws.Range("A2", ws.Range("A" & ws.Rows.Count).End(xlUp)).ClearContents
                    srcWS.Range("A2", srcWS.Range("A" & srcWS.Rows.Count).End(xlUp)).Resize(, 2).Copy ws.Cells(2, 1)
                End If
            Next ws
        Else
            For Each ws In Sheets
                If ws.Name <> "English" Then
                    ws.Rows(Target.Row).EntireRow.Insert
                End If
            Next ws
        End If
    Else
        For Each ws In Sheets
            If ws.Name <> "English" Then
                Target.Offset(0, -1).Resize(, 2).Copy ws.Range("A" & Target.Row)
            End If
        Next ws
    End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    lRowOld = Range("B" & Rows.Count).End(xlUp).Row
End Sub