- 帖子
- 976
- 主題
- 7
- 精華
- 0
- 積分
- 1018
- 點名
- 0
- 作業系統
- Win10
- 軟體版本
- Office 2016
- 閱讀權限
- 50
- 性別
- 男
- 註冊時間
- 2013-4-19
- 最後登錄
- 2026-5-26
|
回復 1# 蘿蔔泥
修改如下紅字，謝謝
所有Workbooks("SNAME") 改為 Workbooks(SNAME)
Sub 完成_總結new()
i = 2
If Range("a2") = "" Then
r = 2
Else
r = Range("a1").End(xlDown).Row + 1
End If
FN = Sheets("設定頁").Range("B5")
Workbooks.Open Filename:=FN
SNAME = ActiveWorkbook.Name
Do While Range("a" & i) <> ""
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 1) = Workbooks(SNAME).Sheets("總表").Range("a" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 2) = Workbooks(SNAME).Sheets("總表").Range("e" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 3) = Workbooks(SNAME).Sheets("總表").Range("f" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 4) = Workbooks(SNAME).Sheets("總表").Range("j" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 5) = Workbooks(SNAME).Sheets("總表").Range("i" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 6) = Workbooks(SNAME).Sheets("總表").Range("d" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 7) = Workbooks(SNAME).Sheets("總表").Range("l" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 8) = Workbooks(SNAME).Sheets("總表").Range("m" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 9) = Workbooks(SNAME).Sheets("總表").Range("q" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 10) = Workbooks(SNAME).Sheets("總表").Range("t" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 11) = Workbooks(SNAME).Sheets("總表").Range("ab" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 12) = Workbooks(SNAME).Sheets("總表").Range("y" & i)
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 13) = Workbooks(SNAME).Sheets("總表").Range("z" & i)
' Workbooks("2.年度訓練計畫表").Worksheets("連結總表").Cells(r, 14) = Workbooks("SNAME").Sheets("總表").Range("aa" & i)
If Workbooks(SNAME).Sheets("總表").Range("j" & i) = "外訓" And Workbooks(SNAME).Sheets("總表").Range("q" & i) <> "" Then
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = "詳如明細"
Else
Workbooks("2.年度訓練計畫表").Sheets("連結總表").Cells(r, 14) = Workbooks(SNAME).Sheets("總表").Range("aa" & i)
End If
r = r + 1
i = i + 1
Loop
Workbooks(SNAME).Close
'x = WorksheetFunction.CountIf(Sheets("連結總表").Range("c:d"), Range("m:m"))
End Sub |
|
發表於 2022-1-5 14:22
| 