避免錯誤, 寫得囉嗦些, 多一些偵錯:
Sub 複製公式()
Dim R&, T$, xA As Range, xB As Range, xE As Range
With Sheets("可出至中午")
R = .[A65536].End(xlUp).Row - 2 '要複製的筆數
If R <= 0 Then MsgBox "無料號! ": Exit Sub
If .[B2] = "已複製" Then MsgBox "已複製過, 請確認! ": Exit Sub
T = .[C2] '公式來源
If T = "" Then MsgBox "請輸入公式來源! ": Exit Sub
Set xA = .[A3].Resize(R) '料號範圍
End With
'------------------------------------
Set xB = [隨手貼!B:B].Find(T, Lookat:=xlWhole)
If xB Is Nothing Then MsgBox "公式來源錯誤! ": Exit Sub
Set xB = [隨手貼!A1:O1].Offset(xB.Row - 1, 0) '公式來源行
'-----------------------------------
Set xE = [日誌!B65536].End(xlUp)(2) 'B欄找最後一筆,並定位下一空白行(2)
xB.Copy xE(1, 0).Resize(R) '複製公式-->A欄(B欄的左1格)
xE(1, 5).Resize(R) = xA.Value '填入料號-->F欄(B欄的右5欄)
[可出至中午!B2] = "已複製" '標示[已複製]避免重覆作業, 要複製新的料號, 先清除B2
End Sub
