vba Excel 2010：下标超出范围错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24386049/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel 2010: Subscript out of range error
提问by DJenkins
The error that I am receiving is Run-time error '9': Subscript out of range.
我收到的错误是运行时错误“9”：下标超出范围。
Sub Workbook_Open()
'Turn off any alerts that maybe displayed.
Application.DisplayAlerts = False
'Turn of the screen updates
Application.ScreenUpdating = False
'Declare the workbook, create it, save it and close it
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:="C:\Saved File\KPI_Grid.xlsm", FileFormat:=52, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
wk.Close
'Open the workbook again. This will get rid of the 'Compatibilty View' and then activate the orginal workbook
Application.Workbooks.Open Filename:="C:\Saved File\KPI_Grid.xlsm"
Workbooks("KPI Grid V5K1 - macro testing.xlsm").Activate
Worksheets("Weekly").Activate
'Select all cells and copy them
Cells.Select
Selection.Copy
'Activate the workbook and sheet that we are going to paste into.
Workbooks("KPI_Grid.xlsm").Activate
Worksheets("Sheet1").Activate ' ******************ERROR HERE ******************
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Activate the previous workbook again.
Workbooks("KPI Grid V5k1 - macro testing.xlsm").Activate
Worksheets("Monthly").Activate
Cells.Select
Cells.Copy
Workbooks("KPI_Grid.xlsm").Activate
Worksheets("Sheet2").Activate
Cells.Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
结束子
I thought this was a relatively simple task.
The aim is to create a new workbook, copy the values of two sheets over and then save and close the new workbook.
Why does this code error?
我认为这是一个相对简单的任务。
目的是创建一个新工作簿，复制两张工作表的值，然后保存并关闭新工作簿。
为什么这段代码会出错？
回答by whytheq
Go to this line Worksheets("Sheet1").Activate
Press F9
Press F5
Check the screen - has the workbook "KPI_Grid.xlsm" been activated ?
Does it actually have a sheet called "Sheet1" ?
转到此行Worksheets("Sheet1").Activate
Press F9
PressF5
检查屏幕 - 工作簿“KPI_Grid.xlsm”是否已激活？
它实际上有一个名为“Sheet1”的工作表吗？
If Sheet1 is not in the same workbook as the code then this will naturally error - you need to ensure everything is qualifiedlike this:
如果 Sheet1 与代码不在同一个工作簿中，那么这自然会出错 - 您需要确保所有内容都符合以下条件：
ActiveWorkbook.Worksheet("Sheet1").Activate
ActiveWorkbook.Worksheet("Sheet1").Activate
