Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim myCol As Long
Dim sRng As Range
Set ws = Sheets("Admin Sheet")
If Not Intersect(Target, Range("N173:N184")) Is Nothing Then
myCol = WorksheetFunction.Match(Target.Value, ws.Range("AC1:BP1"), 0) + 28
Set sRng = ws.Cells(2, myCol).Resize(12, 2)
Application.EnableEvents = False
Select Case Target.Row
Case 173
Range(Cells(171, 1), Cells(182, 2)).Value = sRng.Value
Case 174
Range(Cells(185, 1), Cells(196, 2)).Value = sRng.Value
Case 175
Range(Cells(201, 1), Cells(212, 2)).Value = sRng.Value
Case 176
Range(Cells(215, 1), Cells(226, 2)).Value = sRng.Value
Case 177
Range(Cells(231, 1), Cells(242, 2)).Value = sRng.Value
Case 178
Range(Cells(245, 1), Cells(256, 2)).Value = sRng.Value
Case 179
Range(Cells(261, 1), Cells(272, 2)).Value = sRng.Value
Case 180
Range(Cells(275, 1), Cells(286, 2)).Value = sRng.Value
Case 181
Range(Cells(291, 1), Cells(302, 2)).Value = sRng.Value
Case 182
Range(Cells(305, 1), Cells(316, 2)).Value = sRng.Value
Case 183
Range(Cells(321, 1), Cells(332, 2)).Value = sRng.Value
Case 184
Range(Cells(335, 1), Cells(343, 2)).Value = sRng.Value
End Select
Else
Exit Sub
End If
Application.EnableEvents = True
End Sub
Bookmarks