excel - 使用 vba 将工作表复制到其他工作簿
问题描述
我收到以下代码的“错误 9:下标超出范围”
ActiveWorkbook.Worksheets("Daily Allocation").Copy After:=Workbooks("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\Access_Log.xlsx").Sheets(Workbooks("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\Access_Log.xlsx").Sheets.Count)
完整代码:
Sub DASaveAs()
'MsgBox "SaveAs"
Dim namedate As String
namedate = Date
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets("Daily Allocation").Select
ActiveWorkbook.Worksheets("Daily Allocation").Copy After:=Workbooks("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\Access_Log.xlsx").Sheets(Workbooks("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\Access_Log.xlsx").Sheets.Count)
ActiveSheet.Name = namedate
'Workbooks.Open Filename:="\\Bmcstr01\grp\SRV\Allsrv\2011 CRU\COMPLAINTS ADMIN FOLDER\Daily Allocation.xlsx"
'ActiveWorkbook.Worksheets("Daily Allocation").SaveAs ("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\ER Complaints - " & Format(Now(), "DD.MM.YYYY hhmm AMPM") & ".xlsx")
ActiveWorkbook.Close
Application.ScreenUpdating = True
End Sub
有人可以帮忙吗?
谢谢,弥勒佛
解决方案
您不能复制到工作簿,除非它是打开的。打开后,它的名称不包括文件的路径。所以
Workbooks("\\Bmcstr01\grp\SRV\Allsrv\NEW Complaints Logger\GI Complaints\Spreadsheets\Archieve\Access_Log.xlsx")
应该
Workbooks("Access_Log.xlsx")
推荐阅读
- javascript - 如何限制画线的位置
- macos - 我可以在磁盘映像打开时修改它,但不能“保存更改”吗?
- c++ - How do some libraries override operator[] with multiple return types?
- vue.js - How to save category in laravel 5.7 with vue.js
- javascript - Vue.js - 子事件和返回的完整循环父道具
- r - 结合 get() 函数实现 lapply 以矢量化数据表的合并?R
- matlab - 对于 n = 2 ... 11,设置大小为 m × n 的 Vandermonde 矩阵 A,并为每个 n 给定数据集 a。MATLAB
- python - 尝试安装用 c 编写的包时需要 Microsoft Visual C++ 14.0
- python - 文本和条目宽度不相同 tkinter
- python - python `csv` 库。writerrows(['a', 'b']) 将 2 行放入 csv