PCMAN

Sub ex()

For i = 1 To 31
   With Sheets(CStr(i))
      .Unprotect "12345" '解開保護
      .[S2] = "21:00"
      .[I12] = Sheets("1").[I12].Value
       Sheets("1").[N1:N20].Copy .[N1]
' 一個WITH...END WITH中包含另一個/多個WITH...END WITH

With .[A15:A99].Validation       '在A15至A99之間設立參照絕對值M1至M15的下拉式選單
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$M$1:$M$15"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
       End With
       With .[B15:B99].Validation    '在B15:B99之間設立下拉式選單
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$L$1:$L$15"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
       End With
       With .[F15:F99].Validation   '在F15至F99之間設立下拉式選單
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=$K$1:$K$15"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .IMEMode = xlIMEModeNoControl
        .ShowInput = True
        .ShowError = True
       End With

      .Protect "12345" '設定保護
   End With
Next
End Sub

一次性修改31份相同內容的工作紙內容