Private Sub Worksheet_Deactivate()
For Each ws In ThisWorkbook.Worksheets
If ws.Range("B1") = "仕入先" Then
vOriS = ws.Name: Exit For
End If
Next ws
endrow1 = Worksheets(vOriS).Range("B1048576").End(xlUp).Row
vFormula1 = "=" & vOriS & "!$B$2:$B$" & endrow1
For Each ws In ThisWorkbook.Worksheets
endcolcnt1 = ws.Range("XFD2").End(xlToLeft).Column
For colcnt = 1 To endcolcnt1
If ws.Cells(2, colcnt) = "仕入先" Then
vOs = Split(Cells(1, colcnt).Address, "$")(1)
exit for
End If
Next colcnt
ws.Range(vOs & "1:" & vOs & 149).Validation.Delete
With ws.Range(vOs & "3:" & vOs & 149).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween,
Formula1:=vFormula1
End With
Next ws
End Sub
©2018KanazawaNoSakai |